MySQL常用语句整理

目录

关联查询

子查询

---

 

连接数据库

 # 直接使用明文密码
 mysql -uroot -proot
 # 使用密文密码
 mysql -uroot -p

关闭数据库

 QUIT / Ctrl + D

查看所有数据库

 show database;

创建数据库

 create database db1;

使用数据库

 use db1;

显示所有表

 show tables;

创建用户

 create user '用户名'@'IP地址'

删除用户

 drop user '用户名'@'IP地址';

修改用户

 rename user '用户名'@'IP地址' to '新用户名'@'新IP地址';

修改密码

 set password for '用户名'@'IP地址' = Password("新密码");

查看权限

 show grants for '用户名'@'IP地址';

授权

 grant 权限 on 数据库.表 to '用户名'@'IP地址'

取消授权

 revoke 权限 on 数据库.表 from '用户名'@'IP地址'

创建表

 CREATE TABLE table1(
     id INT NOT NULL AUTO_INCREMENT，
     name VARCHAR(20) NOT NULL,
     age INT NULL,
     PRIMARY KEY('id')
 );

修改表

 ALTER TABLE table1 ADD class VARCHAR(20);

删除列

 ALTER TABLE table1 DROP COLUMN name;

删除表

 DROP TABLE table1;

清空表

 DELETE from user;
 TRUNCATE from user;

增

 insert into user(列名1,列名2 ...) values (值1,值2 ...);
 insert into user(列名1,列名2 ...) values (值1,值2 ...),(值3,值4 ...);

将查询的内容插入

 INSERT INTO table1(id,name) 
 SELECT id,name
 FROM table2;

删

 # 删除所有数据
 delete from user
 # 删除指定内容
 delete from user where 条件

改

 update user set name = "Join" where id > 1;

查

 select * from user
 select * from user where 条件
 select {要查询的字段} from user where 条件

条件查询

 # 与查询
 select * from user where age > 5 and name != 'Wei';
 # 区间查询
 select * from user where age between 18 and 26;
 # 在...中
 select * from user where age in (18,22,25);
 # 不在...中
 select * from user where age not in (17,16,13);
 # 嵌套子查询
 select * from user where age in (select age from 表 where dept = '计算机');

模糊查询

 # 搜索许开头的内容
 select * from user where name like '许%';
 # 搜索‘君’结束的内容
 select * from user where name like '%君';
 # 搜索许开头的内容，但后续只有一个字
 select * from user where name like '许_';
 # 搜索‘君’结束的内容，但前缀只有一个字
 select * from user where name like '_君'

限制查询

 # 查询前10行
 select * from user where limit 10;
 # 查询从第五行后的五行
 select * from user where limit 5 , 5;

排序查询

 # 从小到大排序
 select * from user order by age asc;
 # 从大到小排序
 select * from user order by age desc;

分组查询

 select * from 表 group by dept;
 # group by 必须在 order by之前, where 语句之后

关联查询

交叉查询（CROSS JOIN）

使两张表进行笛卡尔积运算（X和Y的乘积）。会出现大量重复字段

 # 方法1
 select * from user1 u1 , user2 u2 Where 条件;
 # 方法2
 select * from user1 u1 CROSS JOIN user2 u2 Where 条件;

内连接（INNER JOIN）

查询两个表的交集，

 

 SELECT * FROM tb1 INNER JOIN tb2 ON 条件

外连接（LEFT JOIN/RIGHT JOIN）

外连接分为左外连接和右外连接，左外连接以左表为主，先查询左表数据，再通过ON后的关联条件匹配右表，没有则用NULL代替。右外连接同理

左外查询

查询两个表中共有的元素和左表全部的元素，左表有而右表没有的用NULL代替

 

 SELECT u.name , d.name
 FROM user u 
 LEFT JOIN dept d
 ON u.dept = d.id;

右外查询

和左外类似，查询两个表中共有的元素和右表所有的元素，右表有而左表没有的用NULL代替

 

 select u.name,d.dept
      FROM user u
      RIGHT JOIN dept d
      ON u.dept = d.id;

联合查询（UNION/UNION ALL）

联合查询可以将多个查询的结果合并成为一个结果

 select * from user where id = 3
 UNION
 select * from dept where id = 13;
 ​
 # 使用UNION查询;会自动去重
 SELECT u1.name from user1 u1 where sex = '男';
 UNION
 select u2.name from user2 u2 where sex = '女';
 # 使用UNION ALL查询;不会去重
 SELECT u1.name from user1 u1 where sex = '男';
 UNION ALL
 select u2.name from user2 u2 where sex = '女';

• 多条语句的查询列数必须一致

• 每一列的类型和顺序最好一致

• 默认情况下会去除重复项

• UNION ALL不会去重，效率低于UNION

全连接（FULL JOIN）

MySQL默认不支持全连接，可以通过UNION关键字链接左外连接和右外

 select u.name,d.d_name
      FROM user u
      left JOIN dept d
      ON u.dept = d.id
 UNION
 select u.name,d.dept
      FROM user u
      RIGHT JOIN dept d
      ON u.dept = d.id;

 

子查询

子查询是指将一条SQL的结果作为另一条SQL的条件或结果，其中内部嵌套的查询语句称为子查询

子查询的结果有三种，分别是：

1.单行单列

结果为一个值

 select * from user where name = (select name from dept where dept = "Pro");

2.多行单列

结果是一个类似于数组

 select * from user where name in (select name where age > 18);

3.多行多列

结果类似于表，不能用于where条件

 select * from dept d,(select * from emp where join_date > '2019-1-1') e
 where e.dept_id = d.id;
 # 也可以这样
 select d.* , e.* from dept d inner join emp e on d.id = e.dept_id where e.join_date > '2020-1-1';