MySQL--03(增删改查&主键)
格式
insert into 表名（字段名1,字段名2,字段名3.....）values(值1,值2,值3...);
另外一种形式
insert into 表名 values(字段1的值，字段2的值，字段3的值。。。。);
# 这种形式，要求，values必须有所有字段
插入多条数据
insert into 表名（字段1,字段2,字段3.....）values(值1,值2,值3...),(值1,值2,值3...),(值1,值2,值3...);
修改创建好的表字段
MariaDB [employe]> alter table stu change email email varchar(60) not null; Query OK, 2 rows affected, 1 warning (0.06 sec) Records: 2 Duplicates: 0 Warnings: 1
添加新的字段
MariaDB [employe]> alter table stu add height float(3,2); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [employe]> desc stu; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | tel | char(11) | YES | | NULL | | | email | varchar(60) | NO | | NULL | | | ptel | char(11) | YES | | NULL | | | favor | varchar(30) | YES | | NULL | | | height | float(3,2) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec)
# after 是加在某个字段之后；
修改表名
alter table 旧表名 rename to 新表名;
MariaDB [employe]> alter table stu rename to student;
Query OK, 0 rows affected (0.03 sec)
MariaDB [employe]> show create table student; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `tel` char(11) DEFAULT NULL, `email` varchar(60) NOT NULL, `weight` float(3,2) DEFAULT NULL, `ptel` char(11) DEFAULT NULL, `favor` varchar(30) DEFAULT NULL, `height` float(3,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
创建表时添加主键
create table test( id int primary key auto_increment not null, name varchar(30), );
创建表后修改主键
alter table 表名 change 原字段名（要设为主键的字段） 新字段名 int primary key auto_incremnt not null;
复合主键
MariaDB [employe]> create table test( -> id int auto_increment not null, -> name varchar(30), -> primary key(id,name) -> ); Query OK, 0 rows affected (0.02 sec) MariaDB [employe]> desc test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | PRI | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
@todo删除主键
alter table 表名 drop 主键字段名;
命令格式
delete from 表名 where 【条件】
MariaDB [employe]> select * from department; +----+--------+-----------------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +----+--------+-----------------+--------+------+------------+--------+--------+ | 1 | 教务 | 张三丰 | 8 | 9.99 | 2019-01-20 | 总部 | 1 | | 2 | 教务 | 张四丰 | 8 | 9.99 | 2019-02-20 | 总部 | 1 | | 3 | 教务 | 张五丰 | 8 | 9.99 | 2019-03-20 | 总部 | 1 | | 6 | 教务 | 张六丰 | 8 | 9.99 | 2019-04-20 | 总部 | 1 | | 7 | 学生处 | 黄山 | 10 | 8.00 | 2019-04-01 | | 2 | | 8 | 学生处 | 黄玉石 | 10 | 8.00 | 2019-04-01 | | 2 | | 9 | 学生处 | 黄尼古拉斯*赵四 | 10 | 8.00 | 2019-04-01 | | 2 | +----+--------+-----------------+--------+------+------------+--------+--------+ 7 rows in set (0.00 sec) MariaDB [employe]> delete from department where id=2; Query OK, 1 row affected (0.01 sec) MariaDB [employe]> select * from department; +----+--------+-----------------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +----+--------+-----------------+--------+------+------------+--------+--------+ | 1 | 教务 | 张三丰 | 8 | 9.99 | 2019-01-20 | 总部 | 1 | | 3 | 教务 | 张五丰 | 8 | 9.99 | 2019-03-20 | 总部 | 1 | | 6 | 教务 | 张六丰 | 8 | 9.99 | 2019-04-20 | 总部 | 1 | | 7 | 学生处 | 黄山 | 10 | 8.00 | 2019-04-01 | | 2 | | 8 | 学生处 | 黄玉石 | 10 | 8.00 | 2019-04-01 | | 2 | | 9 | 学生处 | 黄尼古拉斯*赵四 | 10 | 8.00 | 2019-04-01 | | 2 | +----+--------+-----------------+--------+------+------------+--------+--------+ 6 rows in set (0.00 sec) MariaDB [employe]>
特别强调，在删除数据时，一定要确认要删除哪些内容
delete from 表名 会删除整个表里的数据
修改记录
update 表名 set 字段名1=要修改值1,字段名2=要修改值2 where [条件]
MariaDB [employe]> select * from department; +----+--------+-----------------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +----+--------+-----------------+--------+------+------------+--------+--------+ | 1 | 教务 | 张三丰 | 8 | 9.99 | 2019-01-20 | 总部 | 1 | | 3 | 教务 | 张五丰 | 8 | 8.00 | 2019-03-20 | 一分司 | 1 | | 6 | 教务 | 张六丰 | 8 | 9.99 | 2019-04-20 | 一分司 | 1 | | 7 | 学生处 | 黄山 | 10 | 8.00 | 2019-04-01 | 总部 | 2 | | 8 | 学生处 | 黄玉石 | 10 | 8.00 | 2019-04-01 | 总部 | 2 | | 9 | 学生处 | 黄尼古拉斯*赵四 | 10 | 8.00 | 2019-04-01 | 总部 | 2 | +----+--------+-----------------+--------+------+------------+--------+--------+ 6 rows in set (0.00 sec) MariaDB [employe]> select dep,master from department; +--------+-----------------+ | dep | master | +--------+-----------------+ | 教务 | 张三丰 | | 教务 | 张五丰 | | 教务 | 张六丰 | | 学生处 | 黄山 | | 学生处 | 黄玉石 | | 学生处 | 黄尼古拉斯*赵四 | +--------+-----------------+ 6 rows in set (0.00 sec)
### 比较运算符查询 < > <= >= != = ### 逻辑运算符 and or not ### 范围查询 between .. and ... 是一个区间 select * from 表名 where 字段名 between 开始日期('2019-04-01') and 结束日期 '2019-05-01' in 是几个点的选择 not ### 模糊查询 like % _ ### 空判断 is null
select * from student where score between 60(包括) and 100(不包括)； 在什么之间
select * from student where score Not between 0（包括） and 60 (不包括）； 在什么间之外
- 分页 limit (n-1)*m, m n,是第几页，m 是每页 显示几条数据 - 排序 select * from 表名 order by 字段名 asc(升序)|desc（降序）; -多字段排序 select * from department order by groups desc,kpi desc; # 多字段排序时，先按第一字段排序，第一段相同的，再按第二字段排序。
group by 分组
MariaDB [employe]> select groups from department group by groups; +--------+ | groups | +--------+ | NULL | | 总部 | | 一分司 | +--------+ 3 rows in set (0.00 sec)
分组 group by + group_concat()
MariaDB [employe]> select groups,group_concat(master) from department group by groups; +--------+----------------------+ | groups | group_concat(master) | +--------+----------------------+ | NULL | 黄尼古拉斯*赵四 | | 总部 | 张三丰,黄山,黄玉石 | | 一分司 | 张五丰,张六丰 | +--------+----------------------+ 3 rows in set (0.00 sec)
分组和统计
MariaDB [employe]> select groups,count(id) from department group by groups; +--------+-----------+ | groups | count(id) | +--------+-----------+ | NULL | 1 | | 总部 | 3 | | 一分司 | 2 | +--------+-----------+ 3 rows in set (0.00 sec) MariaDB [employe]> select groups,avg(kpi) from department group by groups; +--------+----------+ | groups | avg(kpi) | +--------+----------+ | NULL | 8.000000 | | 总部 | 8.663333 | | 一分司 | 8.995000 | +--------+----------+ 3 rows in set (0.00 sec)
分组和 having
MariaDB [employe]> select groups,avg(kpi) from department group by groups having avg(kpi)>8; +--------+----------+ | groups | avg(kpi) | +--------+----------+ | 总部 | 8.663333 | | 一分司 | 8.995000 | +--------+----------+ 2 rows in set (0.00 sec)
