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'@'%';