Mysql数据记录操作

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 …;

　　若值1表

7

5

4

　　值2 为 整数 6

　　值3 为 表

7

添加结果为:

7	6	7
5	6	7
4	6	7

 

2.删除数据delete

　　语法：

　　delete from 表名 ：会清空表，但是不会清空自增字段的offset（偏移量）值

　　truncate table 表名 ：会清空表和自增字段的偏移量；

    删除某一条数据: delete from 表名where 条件;

　　示例：

    delete from mysql.user where password=’’;

3.更新数据update

语法：

    update 表名 set 字段1=值1,字段2=值2  where 条件; 

示例：

    update mysql.user set password=password(‘123’) where user=’root’ and host=’localhost’;

4.查询数据search

1.单表查询

• Select

1.单纯取出

　　select id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;

　　select * from employee;

　　select emp_name,salary from employee;

　　select (now()) 返回当前时间

2.重命名 并 取出字段

　　select字段，字段as 新名字 from employee;

　　select字段，字段 新名字 from employee;

3.避免重复distinct（去掉重复）

　　select distinct post from employee;   

　　select distinct age，sex from employee；去掉两列都完全一样的行

4.通过四则运算查询

　　select emp_name, salary*12 from employee;

　　select emp_name, salary*12 as annual_salary from employee;计算输出并改变字段名

　　select emp_name, salary*12 annual_salary from employee;

5.定义显示格式

　　concat-->连在一起 ,函数用于连接字符串,

　　select concat ('姓名: ',emp_name, '年薪: ', salary*12)  as annual_salary from employee; 

　　concat _ws() 第一个参数为分隔符

　　select concat _ws(':',emp_name,salary*12)  as annual_salary from employee; 

　　结合case语句：

　　select

　　 (

　　　　case

　　　　when emp_name = 'jingliyang' then

　　　　emp_name

　　　　when emp_name = 'alex' then

　　　　concat(emp_name,'_bigsb')

　　　　else

　　　　concat(emp_name, 'sb')

　　　　end

       ) as new_name

　　from employee;

 

例题:按各科平均成绩从低到高和及格率的百分数从高到低顺序；

 select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;

• where

1. 比较运算符：

　　>  <  >=  <=  !=  ==

2. 范围

　　between 80 and 100 值在80到100之间
　　in(80,90,100) 值是80或90或100

3. 模糊匹配

　　# like

　　#'e%'
　　通配符可以是%或_，
　　% 表示任意多字符任意内容
　　_  表示一个字符

　　# regexp：正则

　　# ’^a’

　　# ’g$’ 

4. 查看岗位描述不为null的员工信息

　　select * from employee where post_comment like '%';

　　select * from employee where post_comment is not null;

 

5. 逻辑运算符：

　　在多个条件直接可以使用逻辑运算符 not > and >or

 

6.group by 和 聚合函数

注意：根据谁分组，可以求这个足的总人数，最大值，最小值，平均值，求和 但是这个求出来的值只是和分组字段对应并不和其他任何字段对应，这个时候查出来的所有其他字段都不生效。

         # count 计数

         # max 求最大

         # min 求最小

         # sum 求和

         # avg 求平均

7.having过滤

　　1.！！！执行优先级从高到低：where > group by > having

　　2.Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

　　3.Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数验证

8.order by 查询排序(select执行之后执行)

按单列排序

　　select * from employee order by salary;默认顺序排列

　　select * from employee order by salary asc;顺序排列

　　select * from employee order by salary desc;倒序排列

 

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序

　　select * from employee order BY age, salary DESC; 

　　group by 、having、order by可以联合使用，中间空格隔开，且顺序不能改变

9.limit 限制查询的记录数

示例：

　　select * from employee order by salary desc limit 3; #默认初始位置为0

　　select * from employee order by salary desc limit 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

　　select * from employee order by salary desc limit 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

所有例句：D:\python_learn\day29\mysql例子（用pycharm打开）

注意:

         where条件中不能用select字段的重命名;

         order by / having/group by 可以使用select字段的重命名;实际上order by在select语句之后才执行;但是order by / having/group by经过了mysql的特殊处理,使得它能够感知到select语句中的重命名.

         在执行select语句的时候,实际上是通过where/ order by / having/group by这几个语句锁定对应的行然后循环每一行执行select语句.

2.多表查询

1.连表查询

1.所谓连表

　　总是在链接的时候创建一张大标,里面存放的事两张表的笛卡尔积;再根据条件进行筛选就可以了.

2.表与表之间的连接方式

　　内连接 inner join … on …

　　　　select * from 表1,表2 where 条件;à此方式不常用

　　　　select * from 表1 inner join 表2 on 条件;紫色部分先生成连表,再用条件筛选

　　　　select *from department inner join employee on department.id = employee.dep_id;

　　外连接

　　　　左外连接

　　　　select * from 表1 left_join 表2 on 条件;优先显示左表中的所有数据,然后根据条件和右表匹配;

　　　　右外连接

　　　　select * from 表1 right_join 表2 on 条件;

　　　　全外连接

　　　　左外连接 union 右外连接;

2.子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。

2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3：子查询中可以包含：in、not in、any、all、exsits和 not exsits等关键字

4：还可以包含比较运算符：= 、 !=、> 、<等

注意:

1.exsits关键字

　　exists关键字表示存在。在使用exsits关键字时，内层查询语句不返回查询的记录。

　　而是返回一个真假值。True或False当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

2.any关键字

　　假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，

　　select ...from ... where a > any(...);  -->  select ...from ... where a > result1 or a > result2 or a > result3;

3.ALL关键字

　　ALL关键字与any关键字类似，只不过上面的or改成and。即:

　　select ...from ... where a > all(...);  -->  select ...from ... where a > result1 and a > result2 and a > result3;

4.some关键字

　　some关键字和any关键字是一样的功能。所以:

　　select ...from ... where a > some(...);  -->  select ...from ... where a > result1 or a > result2 or a > result3;