Linux下数据库学习过程之DML（持续更新中）

1.DML

创建一个新表

mysql> create table stu_info(
    -> stu_id int primary key auto_increment comment '学号',
    -> stu_name varchar(32) not null comment '姓名',
    -> stu_age tinyint not null check (stu_age >= 18),
    -> stu_gender char(1) not null default 'M' check (stu_gender in ('F','M'))
    -> );

插入数据

mysql> insert into stu_info values(null,'zhangsan','18','F');     全字段插入
mysql> insert into stu_info(stu_name,stu_age,stu_gender) values('lisa','19','M');  指定字段插入
mysql> insert into stu_info(stu_name,stu_age) values('yangsan','20');   指定字段插入，但不包括非空值的字段
mysql> insert into stu_info values(null,'synatx','21','F'),(null,'policy','22','M'),('2002','liujian','22','M');  多值插入

跨表插值

mysql> create table stu_info2 like stu_info;   建表
mysql> alter table stu_info2 drop  stu_gender;  改表结构
mysql> alter table stu_info2 drop stu_age;   改表结构
mysql> insert into stu_info2 select stu_id,stu_name from stu_info;   插入数据

更新数据

mysql> update stu_info set stu_age = 22; 

替换数据

描述：replace相当于drop+insert

语法格式：replacer  into 表  values   
mysql> replace into stu_info2 values(1,'zhangsan2');
其他待补充

修改数据

语法格式：update 表  set  列及对应值   where  修改位置（一般选择有主键或者唯一键约束的字段）
mysql> update stu_info set stu_age = 20 where stu_id = 2;

删除数据

delete
语法格式：delete from 表 [where 条件]；
mysql> delete from stu_info2 where stu_id = 2002;

truncate
该命令直接清空表中数据
mysql> truncate table stu_info2;

查询数据

select
取一行数据即select一次，而并不是select整个表再选取某一字段（列）
语法：select 字段（列名） from 表 where  条件
mysql> select stu_name from stu_info where stu_id > 2;   单条件
mysql> select stu_name from stu_info where stu_age > 21 and stu_gender = 'M';   多条件（逻辑运算符）
mysql> select *from stu_info where stu_name like 'zhang%';  模糊匹配
mysql> select *from stu_info where stu_id in (1,3,7);   集合匹配
mysql> select *from stu_info where stu_id not  in (1,3,7);  

数字运算
select 10 + 10 ； =20
select 10 +‘10’； =20
select ‘10’ + ‘10’  =20
select 10 + null ； =null    数字与null运算为null
select '10' + null;   =null
select ‘is’ + 10；  =10
select 'is' + 'this';   = 0   字符不能转数字，运算结果为0

查询到的内容对应列名的更改
mysql> select 10+10,stu_name from stu_info where stu_id > 3;   未更改前
+-------+----------+
| 10+10 | stu_name |
+-------+----------+
|    20 | synatx   |
|    20 | policy   |
|    20 | liujian  |
+-------+----------+
3 rows in set (0.00 sec)
mysql> select 10+10 num_sum ,stu_name from stu_info where stu_id > 3;   更改后
+---------+----------+
| num_sum | stu_name |
+---------+----------+
|      20 | synatx   |
|      20 | policy   |
|      20 | liujian  |
+---------+----------+
3 rows in set (0.00 sec)
或者
mysql> select 10+10 as  num_sum ,stu_name from stu_info where stu_id > 3;
+---------+----------+
| num_sum | stu_name |
+---------+----------+
|      20 | synatx   |
|      20 | policy   |
|      20 | liujian  |
+---------+----------+
3 rows in set (0.00 sec)


查询，也可以对表起重名
在这个查询中，给表 stu_info2 起了两个不同的别名 t1 和 t2。然后使用了这两个别名来查询数据。在没有任何连接条件（如 JOIN 条件）的情况下，这种写法会导致每一行都与表中的每一行进行组合，这就是所谓的笛卡尔积（Cartesian product）。
mysql> select 10+10 as  num_sum ,t1.stu_name, t2.stu_name  from stu_info2  as t1  ,stu_info2 as t2;
+---------+----------+----------+
| num_sum | stu_name | stu_name |
+---------+----------+----------+
|      20 | 王五     | 张三     |
|      20 | 李四     | 张三     |
|      20 | 张三     | 张三     |
|      20 | 王五     | 李四     |
|      20 | 李四     | 李四     |
|      20 | 张三     | 李四     |
|      20 | 王五     | 王五     |
|      20 | 李四     | 王五     |
|      20 | 张三     | 王五     |
+---------+----------+----------+
9 rows in set (0.00 sec)

查询---去重
插入多条重复数据
mysql> select *from stu_info2 ;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|      1 | 张三     |
|      2 | 李四     |
|      3 | 王五     |
|      4 | erdan    |
|      5 | erdan    |
|      6 | erdan    |
|      7 | erdan    |
|      8 | erdan    |
|      9 | erdan    |
+--------+----------+
9 rows in set (0.00 sec)
mysql> select distinct stu_name  from stu_info2 ;  其中distinct 针对其后边所有字段
+----------+
| stu_name |
+----------+
| 张三     |
| 李四     |
| 王五     |
| erdan    |
+----------+
4 rows in set (0.00 sec)



正则表达式（后边单开一篇文章学习正则表达式）
正则表达式：regular  expression-> regexp
元字符:
. : 单个的任意字符(默认不包含换行)
\d: 数字：0-9
    补集: \D
\w: ascii: 数字，大写字母，小写字母，以及下划线
    unicode:  数字，大写字母，小写字母，以及下划线，以及大部分的汉字
 补集: \W
\s: 空白字符: 空格，换行，制表符
    补集: \S
\A: 代表字符的开始：但是不匹配任何实际的东西
\Z: 匹配字符的结尾，不匹配任何实际的东西
^: 匹配开始
$：匹配结尾
[abc]: 匹配单个字符，匹配a或者b或者c， 集合
[^abc]: 匹配单个字符,取的是补集，除了abc之外
量词：贪婪和非贪婪
贪婪：尽可能多的
非贪婪：尽可能少的
*: 0次到任意多次，尽可能多的： a*b -> a, aa, aaa, aaaaaaaaaaaaaaaaaaaaa,b
+: 1次多多次 尽可能多的
？：0次或1次 尽可能多的
{m,n}：指定重复的上限和下限
{m,}：
{,n}
*?
+?
??
{}?

select分组，排序，limit

创建新表
员工
mysql> create table employee(
    -> emp_no int primary key auto_increment comment '员工编号',
    -> emp_name varchar(32) not null comment '姓名',
    -> emp_job varchar(32) not null comment '职位',
    -> hire_date datetime not null comment '入职时间',
    -> salary int not null comment '薪资',
    -> bonus int not null comment '奖金',
    -> dept_id int comment '部门编号'
    -> );
部门
mysql> create table department(
    -> dept_id int primary key auto_increment comment '部门编号',
    -> dept_name varchar(64) not null comment '部门名称'
    -> )auto_increment=1001;
    
mysql> alter table employee add constraint emp_dept_fk foreign key(dept_id) references department(dept_id);  创建外键，关联两张表
mysql> insert into department values(null,'科技部'),(null,'后勤部'),(null,'财务部'),(null,'行政部'),(null,'法务部');  给部门表插入数据，因为部门表是作为员工表的主表

员工表
插入数据
mysql> insert into employee values(null,'张三','enginer','2018-09-09','10000','2000','1001'),
    -> (null,'李四','保洁','2024-01-01','6000','1000','1002'),
    -> (null,'吴迪','会计','2023-02-02','5000','2000','1003'),
    -> (null,'柳风','主管','2021-03-03','10000','3000','1004'),
    -> (null,'戚沫','律师','2022-04-04','9000','500','1005'),
    -> (null, '刘五','CEO','2017-05-05',20000,1000,1004);
mysql> insert into employee values(null,'张海风','IT部长','2018-09-09','10000','2000','1001'), (null,'万浩','保安队长','2024-001-01','6000','1000','1002'), (null,'李婉钰','财务部长','2023-02-02','5000','2000','1003'), (null,'刘宇','行政部长','2021-03  03','10000','3000','1004'), (null,'孙烨','法务部长','2022-04-04','9000','500','1005'), (null, '关媚','秘书','2017-05-05',200  0,1000,1004);
mysql> insert into employee values(null,'宋足','enginer','2020-06-06',10000,2000,1001);
mysql> insert into employee values(null,'李灵儿','前台','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'张非','保安','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'陆雨','前台','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'熏儿','前台','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'李莲英','行政组长','2020-06-06',10000,2000,1004);
mysql> insert into employee values(null,'严厉','enginer','2020-06-06',10000,2000,1001);
mysql> insert into employee values(null,'马浩宁','enginer','2020-06-06',10000,2000,1001);
mysql> insert into employee values(null,'高斯','enginer','2020-06-06',10000,2000,1001);
mysql> insert into employee values(null,'小傲','厨师','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'海皇','测评师','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'小砍','律师','2020-06-06',10000,2000,1005);
mysql> insert into employee values(null,'阮阮','剪辑师','2020-06-06',10000,2000,1002);
mysql> insert into employee values(null,'胖迪','博主','2020-06-06',10000,2000,1004);	

按部门编号进行分组
mysql> select dept_id ,avg(salary) from employee group by dept_id;平均薪资
mysql> select dept_id ,max(salary) from employee group by dept_id;最高薪资
mysql> select dept_id ,min(salary) from employee group by dept_id;最低薪资
mysql> select dept_id ,avg((salary+bonus)*12) from employee group by dept_id;年薪
mysql> select dept_id ,count(*) from employee group by dept_id;各部门人数，‘*’指代所有

select与from中间的子句可以是：1group by后的字段；2聚合函数
mysql> select dept_id , emp_job,count(emp_name) from employee group by dept_id,emp_job;

先过滤再分组，where
mysql> select dept_id,count(emp_name) from employee where dept_id in (1001,1002,1003) group by dept_id;
先分组再过滤，having condition （contidion条件和分组结果集有哪些字段有关）
mysql> select dept_id,count(emp_name) from employee where dept_id in (1001,1002,1003) group by dept_id having count(*)>4;
多种结合
mysql> select dept_id,count(emp_name),group_concat(emp_name),avg(salary) from employee where dept_id in (1001,1002,1003) group
p by dept_id having count(*)>4;
order排序，默认升序，即从小到大
mysql> select emp_no ,emp_name, salary from employee order by salary;
mysql> select emp_no ,emp_name, salary from employee order by salary desc;降序
mysql> select emp_no ,emp_name, salary from employee order by salary desc, emp_name desc;按多条字段进行

排序
联合查询
mysql> select *from employee where dept_id in (1001,1002);
现实mysql查询计划（优化器相关）
mysql> explain select *from employee where dept_id in (1001,1002);
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | emp_dept_fk   | NULL | NULL    | NULL |   26 |    57.69 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *from employee where dept_id = 1001 union all select *from employee where dept_id = 1002;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | employee | NULL       | ref  | emp_dept_fk   | emp_dept_fk | 5       | const |    6 |   100.00 | NULL  |
|  2 | UNION       | employee | NULL       | ref  | emp_dept_fk   | emp_dept_fk | 5       | const |    9 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
不去重（union all）
mysql> select emp_name,emp_job,salary from employee where dept_id=1001 union all  select emp_name,emp_job,salary from employee where dept_id=1002;
去重（union）
mysql> select emp_name,emp_job,salary from employee where dept_id=1001 union select emp_name,emp_job,salary from employee where dept_id=1002;

limit限制，使用场景：分页
mysql> select * from employee limit 5;显示前五条数据
mysql> select * from employee limit 1 ,5;从1开始，显示5条数据

多表关联查询，连接
mysql> select *from employee inner join department on employee.dept_id=department.dept_id;内连接
mysql> select *from employee left join department on employee.dept_id=department.dept_id;左连接
mysql> select *from employee right join department on employee.dept_id=department.dept_id;右连接

多表连接

多表连接语法格式
select A.字段，B.字段...from A inner join B on A.字段 =B.字段（共有字段） where 条件；
先把两张表复制一下，复制后的表没有主外键约束，可以方便进行多表连接
mysql> create table emp select *from employee;
mysql> create table dept select *from department;
交叉连接
mysql>  select emp_no,emp_name,hire_date,dept_name from emp,dept;出现很多数据，笛卡尔积
插入一条数据
mysql> insert into emp values(28,'鸡哥','经理','2002-02-02',20000,5000,1010);插入一条没有在dept表中出现过的部门id
多表连接查询
mysql> select emp_no,emp_name,dept_name from emp inner join dept on emp.dept_id=dept.dept_id; 内连接
mysql> select emp_no,emp_name,dept_name from emp left join dept on emp.dept_id=dept.dept_id;左连接
mysql> select emp_no,emp_name,dept_name from emp right join dept on emp.dept_id=dept.dept_id;右连接

自连接

创建新表
mysql> create table zone(
    -> id int primary key ,
    -> parent_id int,
    -> name varchar(32)
    -> );
插入数据
mysql> insert into zone values(1,null,'中国'),(2,1,'陕西'),(3,1,'四川'),(4,2,'西安'),(5,2,'咸阳'),(6,3,'成都'),(7,3,'攀枝花')8,(8,4,'雁塔'),(9,4,'未央'),(10,6,'青羊'),(11,6,'双流');

自连接查询
mysql> select t1.name as 国家 ,t2.name as 省份 from zone as t1 inner join zone as t2 on t1.id=t2.parent_id;
+--------+-----------+
| 国家   | 省份      |
+--------+-----------+
| 中国   | 陕西      |
| 中国   | 四川      |
| 陕西   | 西安      |
| 陕西   | 咸阳      |
| 四川   | 成都      |
| 四川   | 攀枝花    |
| 西安   | 雁塔      |
| 西安   | 未央      |
| 成都   | 青羊      |
| 成都   | 双流      |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> select t1.name as 国家 ,t2.name as 省份 from zone as t1 inner join zone as t2 on t1.id=t2.parent_id and t1.parent_id <=> null;查国家下有哪些省份
+--------+--------+
| 国家   | 省份   |
+--------+--------+
| 中国   | 陕西   |
| 中国   | 四川   |
+--------+--------+
2 rows in set (0.00 sec)

sql执行顺序不同
(1)mysql> select t1.name as 国家 ,t2.name as 省份, t3.name as 区县 from zone as t1 inner join zone as t2 inner join zone as  t3 on t1.id=t2.parent_id and t1.parent_id <=> null and t2.id=t3.parent_id;
//from(t1,t2,t3
//on( t1.id=t2.parent_id and t1.parent_id <=> null and t2.id=t3.parent_id)
//join

(2)mysql> select t1.name as 国家 ,t2.name as 省份, t3.name as 区县 from zone as t1 inner join zone as t2 on t1.id=t2.parent_id and t1.parent_id <=> null inner join zone as t3 on  t2.id=t3.parent_id; 
//from(t1,t2,t3)
//on(t1.id=t2.parent_id and t1.parent_id <=> null)
  on(t2.id=t3.parent_id)
//join(t1,t2)
  join(<t1,t2>,t3)
+--------+--------+-----------+
| 国家   | 省份   | 区县      |
+--------+--------+-----------+
| 中国   | 陕西   | 西安      |
| 中国   | 陕西   | 咸阳      |
| 中国   | 四川   | 成都      |
| 中国   | 四川   | 攀枝花    |
+--------+--------+-----------+
4 rows in set (0.00 sec)

子查询

子查询查询省份
mysql> select t2.name as 省份 from zone as t2 where t2.parent_id in (select t1.id from zone as t1 where t1.parent_id is null);
+--------+
| 省份   |
+--------+
| 陕西   |
| 四川   |
+--------+
2 rows in set (0.00 sec)

mysql> select (select t3.name from zone as t3 where t3.id=t2.parent_id) as 国家,  t2.name as 省份 from zone as t2 where t2.parrent_id in (select t1.id from zone as t1 where t1.parent_id is null);
+--------+--------+
| 国家   | 省份   |
+--------+--------+
| 中国   | 陕西   |
| 中国   | 四川   |
+--------+--------+
2 rows in set (0.00 sec)

这里留一个问题：怎样用子查询的方式把国家省会区县都显示出来

函数

聚合函数
聚合函数对一组值进行运算，并返回单个值。也叫组合函数。
COUNT(*|列名) 统计行数
AVG(数值类型列名) 平均值
SUM (数值类型列名) 求和
MAX(列名) 最大值
MIN(列名) 最小值
除了COUNT()以外，聚合函数都会忽略NULL值

数值型函数
ABS求绝对值
SQRT求平方根
POW 和 POWER两个函数的功能相同，返回参数的幂次方
MOD求余数
CEIL 和 CEILING两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR向下取整，返回值转化为一个BIGINT
RAND生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND对所传参数进行四舍五入
SIGN返回参数的符号

字符串函数

日期和时间函数

流程控制函数

运算符的优先级

待补充

“=”的补充

安全等于：<=>
普通等于：=

测试
创建新表
mysql>  create table test_eq(
    -> eq_id int,
    -> eq_name varchar(32)
    -> );
插入数据
mysql> insert into test_eq values(1,'张三'),(2,null),(3,null);

mysql> select *from test_eq where eq_name = null;    这样查询不能查到内容
Empty set (0.00 sec)

mysql> select *from test_eq where eq_name is  null;   查到内容
+-------+---------+
| eq_id | eq_name |
+-------+---------+
|     2 | NULL    |
|     3 | NULL    |
+-------+---------+
2 rows in set (0.00 sec)

mysql> select *from test_eq where eq_name <=> null;  安全等于可以匹配null，弥补了’=‘
+-------+---------+
| eq_id | eq_name |
+-------+---------+
|     2 | NULL    |
|     3 | NULL    |
+-------+---------+
2 rows in set (0.00 sec)

在linux下，将建表语句到处到一个目录下

[root@名字]# mysqldump -uroot -p密码  数据库  表  >  路径+文件名
或者
[root@名字]# mysql -uroot  -p密码  -e ‘use  数据库名； source   sql文件路径’