一、库操作
1.1 创建库
CREATE DATABASE db_name charset utf8;
1.2 查看数据库
show databases; -- 查看当前用户下所有数据库 show create database db_name; -- 查看数据库的创建信息 select database(); -- 查询当前数据库的名字
1.3 使用数据库
use db_name;
1.4 删除数据库
drop database db_name;
二、用户权限
2.1 用户管理
create user '用户名'@'IP地址' identified by '密码'; -- 创建用户 drop user '用户名'@'IP地址'; -- 删除用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址'; -- 修改用户
2.2 授权管理
show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
三、表操作
3.1 创建表
create table student( id int not null auto_increment primary key, name varchar(250) not null, age int not null, sex enum('男','女') not null default '男', salary double(10,2) not null )engine=innodb default charset=utf8; ps: not null :表示此列不能为空 auto_increment :表示自增长,默认每次增长+1 注意:自增长只能添加在主键或者唯一索引字段上 primary key :表示主键(唯一且不为空) engine =innodb :表示指定当前表的存储引擎 default charset utf8 :设置表的默认编码集
3.2 查看表数据
select 字段1,字段2,字段3 from 表名； -- 查询表数据 desc 表名; -- 查看表结构 show create table 表名; -- 查看表创建信息
3.3 修改表结构
alter table 表名 add 字段名 类型 约束 after 字段1； -- 在字段1的后面添加表字段 alter table 表名 modify 字段 类型 约束； -- modify只改变字段属性 alter table 表名 change 旧字段 新字段 类型 约束; -- change可以改变字段名和属性
alter table 表名 drop 字段名; -- 删除表字段
rename table 旧表名 to 新表名; -- 更新表名
alter table 表名 add primary key(字段); -- 添加主键 alter table 表名 drop primary key; -- 移除主键 alter table 表名 modify id int not null ,drop primary key; -- 自增主键需要先去掉自增才能移除主键
alter table 从表 add constraint fk_test foreign key 从表(字段) referenes 主表(字段); -- 添加外键 alter table 表名 drop foreign key 外键名; -- 移除外键 注意，外键只能先删除再修改
alter table 表名 alter 字段 set default 值; -- 修改默认值
alter table 表名 alter 字段 drop default; -- 删除默认值
3.4 删除表
drop table 表名; -- 删除表
truncate table 表名; -- 清空表
3.5 复制表
#只复制表结构和表中数据 CREATE TABLE tb2 SELECT * FROM tb1; ps:主键自增/索引/触发器/外键 不会 被复制 #只复制表结构 create table tb2 like tb1; ps: 数据/触发器/外键 不会被复制
四、数据操作
4.1 插入数据
insert into 表名(字段1,字段2,字段3 ...) values (值1,值2 ...); -- 按字段插入 insert into 表名 values (值1,值2 ...); -- 按字段顺序插入 insert into 表名 values (值1,值2 ...), (值1,值2 ...), (值1,值2 ...); -- 插入多条数据 insert into 表名(字段1,字段2,字段3 ...) select 字段1,字段2,字段3 ... from 表名； -- 插入查询结果
4.2 更新操作
update 表 set 字段1 = 值1, 字段2 = 值2, ... ; -- 更新整表数据 update 表 set name = 小明; -- 表中说有的名字更新为小明 pdate student set name= '小明' ,age =13 where id = 2; -- 将id=2的名字改为小明，年龄改为13
4.3 删除表
delete from 表名; -- 将表删除
delete from student where id=1; -- 只删除id 为1的数据.
truncate student; -- 清空表
五、单表查询
5.1 简单查询
select * from person; -- 查询person表所有字段
select name,age from person; -- 查询表person的name和age字段
select name as '名字',age '年龄' from person; -- as起别名，as可以不加
select age+10 from person; -- 查询出年龄并加10岁
select distinct age from person; -- 去重
5.2 条件查询
比较运算符
select * from person where age = 23; select * from person where age <> 23; select * from person where age is null; select * from person where age is not null;
逻辑运算符
select * from person where age = 23 and salary =29000; select * from person where age = 23 or salary =29000;
5.3 区间查询
select * from person where salary between 4000 and 8000; -- 包含4000和8000
5.4 集合查询
select * from person where age in(23,32,18);
select * from person where age not in(23,32,18);
5.5 模糊查询
select * from person where name like '张%'; -- 查询姓名以"张"字开头的 select * from person where name like '%张'; -- 查询姓名以"张"字结尾的 select * from person where name like '%张%'; -- 查询名字中含有"张"字的 #查询 name 名称 是四个字符的人 select * from person where name like '____'; #查询 name 名称 的第二个字符是 'l'的人 select * from person where name like '_l%'; #排除名字带 a的学生 select * from student where name not like 'a%'
5.6 排序查询
#按人员工资正序排列,注意:此处可以省略 ASC关键字 是升序 DESC是降序 select * from person order by salary ASC; select * from person order by salary; #工资大于5000的人,按工资倒序排列 select * from person where salary >5000 order by salary DESC; #按中文排序 select * from person order by name; #强制中文排序 select * from person order by CONVERT(name USING gbk); ps:UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序
5.7 聚合函数
select max(age),min(age),avg(age) from person;
5.8 分组查询
#分组查询格式: select 被分组的字段 from 表名 group by 分组字段 [having 条件字段] ps: 分组查询可以与 聚合函数 组合使用. #查询每个部门的平均薪资 select avg(salary),did from person GROUP BY did; #查询每个部门的平均薪资 并且看看这个部门的员工都有谁? select avg(salary),did,GROUP_CONCAT(name) from person GROUP BY did; #GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来 #查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁? select avg(salary),did,GROUP_CONCAT(name) from person GROUP BY did having avg(salary)>10000;
5.9 分页查询
#查询前5条数据 select * from person limit 5; #查询第5条到第10条数据 select * from person limit 5,5; #查询第10条到第15条数据 select * from person limit 10,5; ps: limit (起始条数),(查询多少条数);
5.10 正则表达式
# ^ 匹配 name 名称 以 "e" 开头的数据 select * from person where name REGEXP '^e'; # $ 匹配 name 名称 以 "n" 结尾的数据 select * from person where name REGEXP 'n$'; # . 匹配 name 名称 第二位后包含"x"的人员 "."表示任意字符 select * from person where name REGEXP '.x'; # [abci] 匹配 name 名称中含有指定集合内容的人员 select * from person where name REGEXP '[abci]'; # [^alex] 匹配 不符合集合中条件的内容 , ^表示取反 select * from person where name REGEXP '[^alex]'; #注意1：^只有在[]内才是取反的意思，在别的地方都是表示开始处匹配 #注意2 : 简单理解 name REGEXP '[^alex]' 等价于 name != 'alex' # 'a|x' 匹配 条件中的任意值 select * from person where name REGEXP 'a|x'; #查询以w开头以i结尾的数据 select * from person where name regexp '^w.*i$'; #注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾
5.11 语句执行顺序
select name, max(salary) from person where name is not null group by name having max(salary) > 5000 order by max(salary) limit 0,5
六、多表查询
6.1 多表联合查询
#查询人员和部门所有信息 select * from person,dept;
select * from person,dept where person.did = dept.did;
6.2 多表连接查询
6.21 内连接查询
#查询人员和部门所有信息 select * from person inner join dept on person.did =dept.did;
6.22 左连接查询
#查询人员和部门所有信息 select * from person left join dept on person.did =dept.did;
6.23 右连接查询
#查询人员和部门所有信息 select * from person right join dept on person.did =dept.did;
6.24 全连接查询
#查询人员和部门的所有数据 SELECT * FROM person LEFT JOIN dept ON person.did = dept.did UNION SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;
注意: UNION 和 UNION ALL 的区别:UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果
6.3 复杂条件多表查询