MySQL单表操作
一、新增表
1、新建一个数据库
mysql> create database mysqltest charset utf8; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | images360 | | mysql | | mysqltest | | nodejs | | performance_schema | | sys | +--------------------+ 7 rows in set (0.01 sec) mysql> use mysqltest; Database changed mysql>
2、新建数据表
mysql> create table student( -> id int primary key auto_increment comment "学生id", -> name varchar(100) comment "学生名字", -> age int comment "学生年龄" -> ); Query OK, 0 rows affected (0.03 sec) mysql> show tables; +---------------------+ | Tables_in_mysqltest | +---------------------+ | student | +---------------------+ 1 row in set (0.00 sec)
二、基本表结构操作
- 1、几个基本的词
comment
字段的描素primary key
主键auto_increment
自动增长
2、显示表结构
show create table 表名
mysql> show create table student; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id', `name` varchar(100) DEFAULT NULL COMMENT '学生名字', `age` int(11) DEFAULT NULL COMMENT '学生年龄', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql>
3、修改表名
alter table 原来表名 rename to 新表名;
mysql> show tables; +---------------------+ | Tables_in_mysqltest | +---------------------+ | student | +---------------------+ 1 row in set (0.01 sec) mysql> alter table student rename to student1; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +---------------------+ | Tables_in_mysqltest | +---------------------+ | student1 | +---------------------+ 1 row in set (0.00 sec) mysql>
4、查看表结构
desc student1;
mysql> desc student1; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>
5、修改表字段类型
alter table 表名 modify age 新的数据类型;
mysql> desc student1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> alter table student1 modify age varchar(10); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql>
6、修改表字段名
alter table 表明 change 旧的列名 新的列名 数据类型;
mysql> desc student1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> alter table student1 change name username varchar(100); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student1; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(100) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>
7、新增列
alter table 表名 add 新增的列名 新增列名数据类型;
mysql> alter table student1 add birthday datetime; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student1; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(100) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | birthday | datetime | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql>
8、删除列
alter table 表名 drop 要删除的列名;
mysql> alter table student1 drop birthday; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student1; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(100) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>
三、基本的增、删、改
1、插入数据
insert into 表名(字段名1, 字段名2...) values(值1, 值2....), (值1, 值2....)
mysql> insert into student1(username, age) values("张三", 20); Query OK, 1 row affected (0.00 sec) mysql> insert into student1(username, age) values("李四", 10), ("王五", 30); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql>
2、修改数据
update 表名 set 列名1 = 新值1, 列名2 = 新值2 where 条件 = 条件值;
update student set username = "哈哈" where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+----------+------+ | id | username | age | +----+----------+------+ | 1 | 哈哈 | 20 | | 2 | 李四 | 10 | | 3 | 王五 | 30 | +----+----------+------+ 3 rows in set (0.00 sec) mysql>
3、删除数据
delete from 表名 where 条件 = 条件值1;
delete from student where id = 1; Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+----------+------+ | id | username | age | +----+----------+------+ | 2 | 李四 | 10 | | 3 | 王五 | 30 | +----+----------+------+ 2 rows in set (0.00 sec) mysql>
四、查询语句
1、查询数据
mysql> select * from student1; +----+----------+------+ | id | username | age | +----+----------+------+ | 1 | 张三 | 20 | | 2 | 李四 | 10 | | 3 | 王五 | 30 | +----+----------+------+ 3 rows in set (0.00 sec) mysql> select username, age from student1; +----------+------+ | username | age | +----------+------+ | 张三 | 20 | | 李四 | 10 | | 王五 | 30 | +----------+------+ 3 rows in set (0.00 sec) mysql> select username name, age from student1; +--------+------+ | name | age | +--------+------+ | 张三 | 20 | | 李四 | 10 | | 王五 | 30 | +--------+------+ 3 rows in set (0.00 sec) mysql>
2、排序查询
select * from 表名 order by 字段名 desc/asc
mysql> select * from student1 order by age desc; +----+----------+------+ | id | username | age | +----+----------+------+ | 3 | 王五 | 30 | | 1 | 张三 | 20 | | 2 | 李四 | 10 | +----+----------+------+ 3 rows in set (0.00 sec) mysql> select * from student1 order by age asc; +----+----------+------+ | id | username | age | +----+----------+------+ | 2 | 李四 | 10 | | 1 | 张三 | 20 | | 3 | 王五 | 30 | +----+----------+------+ 3 rows in set (0.01 sec) mysql>
3、分页查询
select * from 表名 limit 开始位置, 取几条数据
mysql> select * from student limit 0, 2; +----+----------+------+ | id | username | age | +----+----------+------+ | 1 | 张三 | 20 | | 2 | 李四 | 10 | +----+----------+------+ 2 rows in set (0.00 sec) mysql>
4、定义别名查询(可以定义字段及表)
mysql> select username as "名字", age as "年龄" from student; +--------+--------+ | 名字 | 年龄 | +--------+--------+ | 李四 | 10 | | 王五 | 30 | +--------+--------+ 2 rows in set (0.01 sec) mysql> select username "年龄", age "年龄" from student; +--------+--------+ | 年龄 | 年龄 | +--------+--------+ | 李四 | 10 | | 王五 | 30 | +--------+--------+ 2 rows in set (0.00 sec) mysql>
5、字段可以运算
mysql> select username, age + 10 from student; +----------+----------+ | username | age + 10 | +----------+----------+ | 李四 | 20 | | 王五 | 40 | +----------+----------+ 2 rows in set (0.00 sec) mysql>
6、
where
的条件查询mysql> select id,username,age from student where id = 2; +----+----------+------+ | id | username | age | +----+----------+------+ | 2 | 李四 | 10 | +----+----------+------+ 1 row in set (0.01 sec) mysql> select id,username,age from student where age < 20; +----+----------+------+ | id | username | age | +----+----------+------+ | 2 | 李四 | 10 | | 4 | 张三 | 18 | +----+----------+------+ 2 rows in set (0.00 sec) mysql>
7、聚合函数的使用
1.新建数据
mysql> create table student( -> id int primary key auto_increment comment "用户id", -> name varchar(100) not null unique comment "名字", -> city varchar(100) not null comment "城市", -> age int not null comment "年龄", -> create_at timestamp default current_timestamp comment "创建时间" -> ); Query OK, 0 rows affected (0.02 sec) mysql> insert into student(name, city, age) values ("张三", "广东", 10), ("李四", "广西", 22), ("王五", "广东", 25), ("马六", "湖南", 40); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+--------+--------+-----+---------------------+ | id | name | city | age | create_at | +----+--------+--------+-----+---------------------+ | 1 | 张三 | 广东 | 10 | 2018-06-29 14:31:08 | | 2 | 李四 | 广西 | 22 | 2018-06-29 14:31:08 | | 3 | 王五 | 广东 | 25 | 2018-06-29 14:31:08 | | 4 | 马六 | 湖南 | 40 | 2018-06-29 14:31:08 | +----+--------+--------+-----+---------------------+ 4 rows in set (0.00 sec) mysql>
2.
count(列名)
计数select count(*) from 表名;
mysql> select count(name) from student; +-------------+ | count(name) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec) mysql>
3.
sum(列名)
总共select sum(age) as 年龄总共 from 表名;
mysql> select sum(age) as 年龄总共 from student; +--------------+ | 年龄总共 | +--------------+ | 97 | +--------------+ 1 row in set (0.01 sec) mysql>
4.
avg(列名)
求平均mysql> select avg(age) as 平均年龄 from student; +--------------+ | 平均年龄 | +--------------+ | 24.2500 | +--------------+ 1 row in set (0.00 sec) mysql>
5.
max(列名)
求最大值mysql> select max(age) from student; +----------+ | max(age) | +----------+ | 40 | +----------+ 1 row in set (0.01 sec) mysql>
6.
min(列名)
求最小值mysql> select min(age) from student; +----------+ | min(age) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql>
8、使用正则查询
mysql> select * from student where city regexp "^广"; +----+--------+--------+-----+---------------------+ | id | name | city | age | create_at | +----+--------+--------+-----+---------------------+ | 1 | 张三 | 广东 | 10 | 2018-06-29 14:31:08 | | 2 | 李四 | 广西 | 22 | 2018-06-29 14:31:08 | | 3 | 王五 | 广东 | 25 | 2018-06-29 14:31:08 | +----+--------+--------+-----+---------------------+ 3 rows in set (0.00 sec) mysql>
五、having
的使用
1、使用
where
过滤数据会报错mysql> select id, name, age as 年龄 from student where 年龄 > 30; ERROR 1054 (42S22): Unknown column '年龄' in 'where clause' mysql>
2、使用
having
替换where
mysql> select id, name, age as 年龄 from student having 年龄 > 30; +----+--------+--------+ | id | name | 年龄 | +----+--------+--------+ | 4 | 马六 | 40 | +----+--------+--------+ 1 row in set (0.00 sec) mysql>
六、distinct
去重查询
1、数据表数据
mysql> select name from student; +--------+ | name | +--------+ | 张三 | | 李四 | | 王五 | | 马六 | | 张三 | +--------+ 5 rows in set (0.00 sec) mysql>
2、使用去重查询
mysql> select distinct name from student; +--------+ | name | +--------+ | 张三 | | 李四 | | 王五 | | 马六 | +--------+ 4 rows in set (0.00 sec) mysql>