基本sql语句

1.操作数据库

#创建数据库
CREATE DATABASE exercise;
#创建不存在的数据库
CREATE DATABASE IF NOT EXISTS exercise;
#查看所有数据库
SHOW DATABASES;
#查看某个数据库的定义信息
SHOW CREATE DATABASE exercise;
#修改数据库字符信息
ALTER DATABASE exercise CHARACTER SET utf8;
#删除数据库
DROP DATABASE exercise;

2.操作表

#创建表
CREATE TABLE student(
	id INT,
	`name` VARCHAR(32),
	age INT,
	score DOUBLE(4,1),
	birthday DATE,
	insert_time TIMESTAMP
);
#查看表信息
DESC student;
#查看创建表的sql语句
SHOW CREATE TABLE student;
#修改表的名称
ALTER TABLE student RENAME TO students;
#添加一列
ALTER TABLE students ADD nick_name VARCHAR(32);
#删除列
ALTER TABLE students DROP nick_name;
#删除表
DROP TABLE students;
DROP TABLE IF EXISTS students;

3.操作表中的数据

增加 insert into

#写全所有列名
INSERT INTO student(id,`name`,age,score,birthday,insert_time)
		VALUES(1,'xwz',22,80.8,'20020101',NOW());
#不写列名(所有的列都添加)
INSERT INTO student VALUES(2,'xwz',21,90.0,'20021215',NOW());
#插入部分数据
INSERT INTO student(id,`name`,insert_time) 
	VALUES(3,'jesse',NOW());

删除 delete

#删除表中的数据
DELETE FROM student WHERE id = 2;
#删除表中的所有数据
DELETE FROM student;
#删除表中的所有数据(高效 先删除表，然后再创建一张一样的表)
TRUNCATE TABLE student;

修改 update

#不带条件修改(会修改表中该列的所有记录)
UPDATE student SET age = 20;
#带条件修改
UPDATE student SET age = 25 WHERE `name` = 'jesse';

查询 select

between and 和 in

#范围查询
SELECT * FROM student WHERE age>=20 && age<=30;
SELECT * FROM student WHERE age>=20 AND age<=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

#多值查询
SELECT * FROM student WHERE age=15 OR age=20 OR age=25; 
SELECT * FROM student WHERE age IN(15,20,25);

is not null 和 like 和 distinct

#查询不为空
SELECT * FROM student WHERE score IS NOT NULL;

#模糊查询 _:单个任意字符  %:多个任意字符
#名字以x开头
SELECT * FROM student WHERE `name` LIKE 'x%';
#名字第二个是e
SELECT * FROM student WHERE `name` LIKE '_e%';
#名字包含3个字
SELECT * FROM student WHERE `name` LIKE '_____';
#名字包含w
SELECT * FROM student WHERE `name` LIKE '%w%';

#distinct
SELECT DISTINCT `name` FROM student;

order by

#order by 
#默认升序
SELECT * FROM student ORDER BY age;
#降序
SELECT * FROM student ORDER BY age DESC;

聚合函数

#count max min sum avg
SELECT SUM(age) FROM student;
SELECT AVG(age) FROM student;
SELECT COUNT(*) FROM student;
SELECT MIN(age) FROM student;
SELECT MAX(age) FROM student;

分组查询 group by

#按名称分组,统计平均值
SELECT `name`,AVG(score) FROM student GROUP BY `name`;
#按名称分组,统计平均值、个数
SELECT `name`,AVG(score),COUNT(*) FROM student GROUP BY `name`;
SELECT `name`,AVG(score),COUNT(id) FROM student GROUP BY `name`; 
#按名称分组,统计平均值、个数  但分数必须大于75
SELECT `name`,AVG(score),COUNT(*) FROM student WHERE score>75 GROUP BY `name`;
#按名称分组,统计平均值、个数  但分数必须大于75 且 人数分组人数大于1
SELECT `name`,AVG(score),COUNT(*) FROM student WHERE score>75 GROUP BY `name` HAVING COUNT(*)>1;

分页查询 limit

SELECT * FROM student LIMIT 0,1;
SELECT * FROM student LIMIT 1,1;
SELECT * FROM student LIMIT 2,1;

**内连接查询 **

#查询员工姓名、性别、部门名称（隐式内连接）
SELECT emp.name,emp.gender,dept.name 
FROM emp,dept 
WHERE emp.dept_id = dept.id; 

SELECT t1.name,t1.gender,t2.name 
FROM emp t1,dept t1
WHERE t1.dept_id = t2.id;

#显式内连接
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;

外连接

#左外连接
SELECT * FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
#右外连接
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

子查询

#查询最高的员工工资信息
SELECT MAX(salary) FROM emp;#9000
SELECT * FROM emp WHERE salary = 9000;

SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

#工资小于平均工资的人(子查询结果是单行单列)
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
#查询市场部和财务部的所有信息(子查询结果是多行单列)
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` = '财务部' OR `name` = '市场部');
#查询某日期后加入的员工信息和部门信息(子查询结果是多行多列)
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t2.join_time > '20021020';#普通内连接
SELECT * FROM dept t2,(SELECT * FROM emp WHERE join_time > '20021020') t1 WHERE t1.dept_id = t2.id;

4.数据控制语言

管理用户

#添加用户
CREATE USER 'xwz'@'%' IDENTIFIED BY 'xwz';
#删除用户
DROP USER 'xwz'@'%';

权限管理

#添加用户
CREATE USER 'xwz'@'%' IDENTIFIED BY 'xwz';
#删除用户
DROP USER 'xwz'@'%';

#查询权限
SHOW GRANTS FOR 'xwz'@'%';
#授予权限
GRANT SELECT ON exercise.student TO 'xwz'@'%';
GRANT ALL ON *.* TO 'xwz'@'%';
#撤销权限
REVOKE UPDATE ON exercise.student FROM 'xwz'@'%';