开源数据库MySQL DBA运维实战 第3章 SQL2
一、DML
1.INSERT ---插入数据
语法:INSERT INTO 表名 VALUES (值1,值2,值3…值n);
部分插入语法:语法:INSERT INTO 表名(列名,列名)VALUES (值1,值2);
2.UPDATE ---更新数据
语法:UPDATE 表名 SET 列名=值 WHERE CONDITION;
示例:
准备一张表:
mysql> create table t6(id int, name varchar(20));
mysql> insert into t6 values (1,'aa');
mysql> insert into t6 values (2,'bb');
更新数据:把bb改成cc
mysql> update t6 set name='cc' where id=2;
查询结果:mysql> select * from t6;
3.DELETE ---删除数据
语法:DELETE FROM 表名 WHERE CONDITION;
示例:需求:删除id为2 的用户记录
mysql> delete from t6 where id=2;
二、DQL
1.简单查询
查看所有列:SELECT * FROM 表名;
查部分列:SELECT 列1,列2,列3 FROM 表名;
2.条件查询
1)单条件查询where
示例:查询hr部门的员工姓名
SELECT name,post FROM employee5 WHERE post='hr';
2)多条件查询AND/OR
示例:查询hr部门的员工姓名,并且工资大于1000
SELECT name,salary FROM employee5
WHERE post='hr' AND salary>1000;
3)关键字BETWEEN AND 在什么之间
示例:需求:查一查薪资在5000到15000
SELECT name,salary FROM employee5
WHERE salary BETWEEN 5000 AND 15000;
4)关键IN集合查询
示例:工资可能是4000,也可能是5000,还有可能是9000,
SELECT name, salary FROM employee
WHERE salary NOT IN (4000,5000,6000,9000) ;
5)关键字IS NULL
没有岗位描述的
空:SELECT name,job_description FROM employee5
WHERE job_description IS NULL;
非空
SELECT name,job_description FROM employee5
WHERE job_description IS NOT NULL;
6)关键字LIKE模糊查询
示例:好像有个员工姓阿
SELECT * FROM employee5
WHERE name LIKE 'al%';
3.查询排序
例如以工资升序排列:
SELECT * FROM 表名 ORDER BY 工资的列名 ASC;
例如以工资降序排列:
SELECT * FROM 表名 ORDER BY 工资的列名 DESC;