TIP

limit 1,10 就是从第一条开始，取10 条users 的记录

收到客户端{pageNo:1,pagesize:10}
select * from table limit (pageNo-1)*pageSize, pageSize;

收到客户端{pageNo:5,pageSize:30}
select * from table limit (pageNo-1)*pageSize,pageSize;

内部联结 JOIN

内部联结又称为等值联结，它基于两个表之间的相等测试。

SELECT
	vend_name,
  prod_name,
  prod_price
FROM vendors v
JOIN products p
	USING (vend_id)
ORDER BY vend_name, prod_name

-- 自联结
SELECT
	p1.prod_id,
  p1.prod_name
FROM products p1
JOIN products p2
	ON p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR'

外部联结

SELECT
	c.cust_id,
  o.order_num
FROM customers c
LEFT JOIN orders o
	ON c.cust_id = o.cust_id

聚集函数

SELECT
	c.cust_name,
  c.cust_id,
  count(o.order_num) as num_ord
FROM customers c
LEFT JOIN orders o
	ON c.cust_id = o.cust_id
GROUP BY c.cust_id

UNION

在某种程度上 UNION 可以大幅简化 WHERE 子句。

UNION 中每个查询必须包含相同的列、表达式或聚集函数。

-- 指定 ALL 不会取消重复的行
SELECT
	vend_id,
  prod_id,
  prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT
	vend_id,
  prod_id,
  prod_price
FROM products
WHERE vend_id IN (1001, 1002)

INSERT

指令	已存在	不存在	举例
insert	报错	插入	insert into names(name, age) values(“小明”, 23);
insert ignore	忽略	插入	insert ignore into names(name, age) values(“小明”, 24);
replace	替换	插入	replace into names(name, age) values(“小明”, 25);

插入数据最好指定列名，可跳过默认取默认值的列且可更改顺序。

多行数据以 , 分隔。

INSERT INTO customers
	(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES
	('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'),
  ('M. Martian', '42 Galaxy', 'New York', 'NY', '11213', 'USA')

UPDATE

更新语句由三部分组成：

• 要更新的表
• 列名和它们的新值
• 确定要更新行的过滤条件

UPDATE customers
SET 
	cust_name = 'The Fudd',
	cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005

DELETE

注意检查 WHERE 有没有使用。

DELETE FROM customers
WHERE cust_id = 10006

Mysql常用命令

数据定义语句(DDL)

数据库操作

• 登录数据库：mysql -uroot -proot
• 创建数据库：create database test
• 查看所有数据库：show databases
• 使用数据库：use test
• 查看所有数据表：show tables
• 删除数据库：drop database test

表操作

• 创建表：create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2))
  create table dept(deptno int(2),deptname varchar(10))
• 查看表的定义：desc emp
• 查看表定义（详细）：show create table emp \G
• 删除表：drop table emp
• 修改表字段：alter table emp modify ename varchar(20)
• 添加表字段：alter table emp add column age int(3)
• 删除表字段：alter table emp drop column age
• 字段改名；alter table emp change age age1 int(4)
• 修改表名：alter table emp rename emp1

数据操纵语句(DML)

插入记录

• 指定名称插入：insert into emp (ename,hiredate,sal,deptno) values ('zhangsan','2018-01-01','2000',1)
• 不指定名称插入：insert into emp values ('lisi','2018-01-01','2000',1)
• 批量插入数据：insert into dept values(1,'dept1'),(2,'dept2')

修改记录

update emp set sal='4000',deptno=2 where ename='zhangsan'

删除记录

delete from emp where ename='zhangsan'

查询记录

• 查询所有记录：select * from emp
• 查询不重复的记录：select distinct deptno from emp
• 条件查询：select * from emp where deptno=1 and sal<3000
• 排序和限制：select * from emp order by deptno desc limit 2
• 聚合(查询部门人数大于1的部门编号)：select deptno,count(1) from emp group by deptno having count(1) > 1
• 连接查询：select * from emp e left join dept d on e.deptno=d.deptno
• 子查询：select * from emp where deptno in (select deptno from dept)
• 记录联合：select deptno from emp union select deptno from dept

数据控制语句(DCL)

• 授予操作权限：grant select,insert on test.* to 'test'@'localhost' identified by '123'
• 收回操作权限：revoke insert on test.* from 'test'@'localhost'

其他

字符集相关

• 查看字符集：show variables like 'character%'
• 创建数据库时指定字符集：create database mall character set utf8

修改时区

• 修改mysql全局时区为北京时间，即我们所在的东8区：set global time_zone = '+8:00';
• 修改当前会话时区：set time_zone = '+8:00'
• 立即生效：flush privileges

权限相关

• 授予所有数据库的所有权限：grant all privileges on *.* to user_name@localhost identified by '123'
• 授予所有数据库的所有权限(包括grant)：grant all privileges on *.* to user_name@localhost with grant option
• 授予SUPER PROCESS FILE权限：grant super,process,file on *.* to user_name@localhost
• 只授予登录权限：grant usage on *.* to user_name@localhost
• 查看账号权限：show grants for user_name@localhost
• 修改自己的密码：set password = password('123')
• 管理员修改他人密码：set password for 'user_name'@'localhost' = password('123')
• 删除账号：drop user user_name@localhost