mysql进阶语法_MySQL 之 进阶操作

1、数据操作

(1)insert 增加数据

1. 插入完整数据(顺序插入) 语法一： INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n); 语法二： INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据 语法： INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录 语法： INSERT INTO 表名 VALUES (值1,值2,值3…值n), (值1,值2,值3…值n), (值1,值2,值3…值n); 4. 插入查询结果 语法： INSERT INTO 表名(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2 WHERE …;

(2)delete 删除数据

语法： DELETE FROM 表名 WHERE CONITION;示例： DELETE FROM mysql.user WHERE password=’’;

(3)update 修改数据

语法： UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE CONDITION;示例： UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;

(4)select 查询数据

<1> 单表查询

<2> 联表查询

语法SELECT DISTINCT FROM 表名;

2、单表查询

单表查询的语法SELECT DISTINCT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数; 单表查询的关键字执行的优先级 from where group by select distinct having order by limit 单表查询的执行过程 1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.执行select(也可以distinct去重) 5.将分组的结果进行having过滤 6.将结果按条件排序：order by 7.限制结果的显示条数

(1)前期建数据库和表

# 建表和数据准备 # 创建表create table employee(id int not null unique auto_increment,emp_name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int); # 插入记录insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values#以下是教学部('cai','male',78,'20150302','teacher',1000000.31,401,1),('liu','male',81,'20130305','teacher',8300,401,1),('ling','male',73,'20140701','teacher',3500,401,1),('yongliang','male',28,'20121101','teacher',2100,401,1),('marry','female',18,'20110211','teacher',9000,401,1),('tom','male',18,'19000301','teacher',30000,401,1),('liangliang','male',48,'20101111','teacher',10000,401,1),#以下是销售部门('阿哥','female',48,'20150311','sale',3000.13,402,2),('丫头','female',38,'20101101','sale',2000.35,402,2),('小丁','female',18,'20110312','sale',1000.37,402,2),('月亮','female',18,'20160513','sale',3000.29,402,2),('太阳','female',28,'20170127','sale',4000.33,402,2),#以下是运营部门('老才','male',28,'20160311','operation',10000.13,403,3), ('老李','male',18,'19970312','operation',20000,403,3),('老王','female',18,'20130311','operation',19000,403,3),('老德','male',18,'20150411','operation',18000,403,3),('老赵','female',18,'20140512','operation',17000,403,3);

(2)简单查询

# 查看表中所有select * from 表; # 指定列查询select emp_name,salary from employee;# 在列中使用四则运算select emp_name,salary*12 from employee;# 重命名select emp_name,salary*12 as annul_salary from employee;select emp_name,salary*12 annul_salary from employee;# 去重 distinctselect distinct post from employee;select distinct sex,post from employee;# 定义显示格式 concat()函数 # 用于拼接字符串select concat('姓名 ：',emp_name),concat('年薪:',salary*12) from employee; concat_ws()函数 第一个参数为分隔符select concat_ws('|','a','b','c');# 条件判断case when语句 == if条件判断句select ( case when emp_name = 'cai' then emp_name when emp_name = 'liu' then CONCAT(emp_name,'_haha') else concat(emp_name, 'hehe') end ) as new_name from employee;

(3)根据条件筛选行 where

# 比较运算 = > < >= <= !=.........