DML（data manipulation language）
SQL2
1、DML（data manipulation language）
DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE
插入数据INSERT
INSERT INTO 表名 VALUES (值1,值2,值3…值n); --完整插入
INSERT INTO 表名（列名，列名） VALUES (值1,值2); ---部分插入
更新数据UPDATE
UPDATE 表名 SET 列名=值 WHERE CONDITION; --- 语法
update t6 set name='cc' where id=2;
update mysql.user set authentication_string=password("QianFeng@123456") where user="root"; -- 更改数据库密码
flush privileges -- 更新数据库
删除数据DELETE
DELETE FROM 表名 WHERE CONDITION; --- 语法
delete from t6 where id=2;
更改表的结构
alter table t2 add xingbie enum('nan','nv'); --- 增加列
增加列：alter table t2 drop xingbie ; --- 减少列
2、DQL语句 数据库查询语言
（1）目的
在MySQL管理软件中，可以通过SQL语句中的DQL语言来实现数据的
（2）MySQL查询
准备环境
CREATE TABLE company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
); ---- 结构语句desc employee5; --- 查看表结构
insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('aofa','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102); ----插入数据
简单查询
SELECT * FROM 表名; --- 查看所有列
SELECT 列1，列2，列3 FROM 表名; --- 查看部分列
SELECT name, salary, salary*14 FROM employee5; --- 查看年薪
SELECT name, salary, salary*14 as 年薪 FROM employee5; ---查看个性化的年薪
条件查询
单条件查询where
SELECT name,post FROM employee5 WHERE post='hr';
多条件查询AND/OR
SELECT name,salary FROM employee5 WHERE post='hr' AND salary>1000;
SELECT name, salary FROM employee5 WHERE salary=6000 OR salary=8000
关键字BETWEEN AND 在什么之间
SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
关键字IN集合查询
SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM employee WHERE salary NOT IN (4000,5000,6000,9000) ;
关键字IS NULL
SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
SELECT name,job_description FROM employee5 WHERE job_description=''; --错误示范
关键字LIKE模糊查询
SELECT FROM employee5 WHERE name LIKE 'al%'; //通配符’%’代表多个任意字符,注意不是shell的""星号。mysql使用"%" ---模糊查找‘姓a’
SELECT * FROM employee5 WHERE name LIKE 'al___'; // 注意不是shell的“？”问号。mysql使用下划线 -
查询排序
升序排列
SELECT * FROM 表名 ORDER BY 工资的列名 ASC;
降序排列
SELECT * FROM 表名 ORDER BY 工资的列名 DESC;
查询前几位
SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0