MySQL基础

最新推荐文章于 2023-03-15 11:54:37 发布

enla5

最新推荐文章于 2023-03-15 11:54:37 发布

阅读量458

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_41432808/article/details/120730932

版权

MySQL

是一种基于ANSI的标准计算机语言

一：数据查询语言（DQL:Data Query Language）：其语句，也称为“bai数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY 和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。二：数据操作语言（DML：Data Manipulation Language）： 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。三：事务处理语言（TPL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT 和ROLLBACK。四：数据控制语言（DCL）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用 GRANT或REVOKE控制对表单个列的访问。五：数据定义语言（DDL）：其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。六：指针控制语言（CCL）：它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

一.数据库操作(CMD命令)

1.退出操作

exit; quit; \q;

2.显示已有数据库

show databases;

3.创建数据库

create database 数据库名字;

create database if not exists ;数据库名字;若没有该数据库名字则创建该数据库

4.删除数据库

drop database 数据库名字;

5.查看创建数据库的命令(了解)

show create database 数据库名字;

6.选择数据库

use 已有数据库名字;

7.查看已有的表

show tables;

8. 创建表的结构

create table 表名(
    id int,
    name varchar(30),
    age int
);

9.查看表接口

desc 表名;

describe 表名;

show columns from 表名;

10.对数据表中的操作(增删改查)

在每个输出命令行后加上\G会使查询结果美观
查询数据库中的user表内容 select * from user;
- 删除数据库user表 drop table user;
- 查看创建表命令 show create table user;或者show create table user\G;
创建数据库并设置编码格式 create database mytest default character set utf8;

11.约束操作

NOT NULL：非空，用于保证该字段的值不能为空。如，姓名，学号
DEFAULT：默认，用于保证该字段有默认值。如，性别
ＰＲＩＭＡＲＹ　KEY：主键，用于保证该字段的值具有唯一性，并且非空。如，学号，员工编号
ＵＮＩＱＵＥ：唯一，用于保证该字段的值具有唯一性，可以为空。如，座位号
ＦＯＲＥＩＧＮ　ＫＥＹ：****自于主表的关联列的值在从表添加外键约束，用于引用主表中某列的值
auto_increment：自动增长（自己增加1）
ｕｎｓｉｇｎｅｄ：无符号位（即指是正数）

12.修改表的操作

修改表类型
增加字段
删除表字段
字段改名
修改数据库编码：alter　database　ｍｙtest　character　ｓｅｔ　ｕｔｆ8;

: create database mytest character set utf8;

13.DDL，ＤＭＬ和ＤＱＬ;

基本语法：

INSERT INTO tablename(field1,field2,...fieldn) VALUES(value1,value2,...,valuen);

注意事项：

可以不用指定列名，但是values后面的顺序应该和字段的排列顺序一致；

含可空的字段、非空但含有默认值的字段以及自增字段，可以不在insert后的字段列表里面出现，values后面对应字段名称的值；·

INSERT INTO tablename(field1,field2,...fieldn) VALUES

(record1_value1,record1_value2,...,record1_valuen)，

(record2_value1,record2_value2,...,record2_valuen)，

...

(recordn_value1,recordn_value2,...,recordn_valuen)

-- DDL :

--1. 修改表的字段类型：

alter table cms_admin modify

-- 将 age 字段类型和约束改为 tinyint unsigned not null default 20

alter table cms_admin modify age tinyint unsigned not null default 20;

-- 2. 添加表字段：

alter table cms_admin add

-- 添加 addr varchar(30) not null

alter table cms_admin add addr varchar(30) not null after age;

-- 3 删除字段：

alter table cms_admin drop

-- 删除 addr email alter table cms_admin drop addr,drop email;

-- 4 更改字段名：

alter table cms_admin change 旧字段新字段

-- 修改 name字段为 userName

alter table cms_admin change name userName varchar(30) not null;

-- 5. 修改字段的排序：

alter table cms_dmin modify|add|change [first|after 字段]

-- 将 salary 字段放到 userName 字段的后面

alter table cms_admin modify salary decimal(10,2) not null default 1000 after userName;

-- 6.更改表名：

alter table cms_admin rename [to] 新表名

-- 将cms_admin 改名为 cms_ad

alter table cms_admin rename to cms_ad;

-- 7.修改数据库编码：

alter database mytest character set utf8;

create databases mytest character set utf8;

--8.DML和DQL ：

表的内容的操作: insert（添加） update(更新) delete(删除) select(查询)

-- 1. insert [into]：带字段的单行和多行添加，不带字段的添加必须按顺序依次填写字段值

insert cms_ad values(9,'jhon',8888.88,'2323',22)

-- 2. update 表 set 字段=值,字段=值,... where 字段

-- 更新编号是3 的记录中 age字段值为27

-- 注意如果更新没有条件，那age 这一列的所有的都修改为要更新的值

update cms_ad set age = 27 where id=3;

-- 更新所有age字段的值都加 1

update cms_ad set age = age + 1;

-- 3 delete from 表 [where 字段]

-- 删除编号6 的记录

delete from cms_ad where id=6;

-- 清空表内容 -- 注意 auto_increment 不能重置 delete from cms_ad;

insert cms_ad (userName,password) values ('tom','1234'), ('alice','1234'), ('tina','14');

alter table cms_ad auto_increment=1;

-- 清空表内容 -- 注意 auto_increment 能重置 truncate cms_ad;

-- 4. select 字段1,字段2....from cms_ad;

-- 1. 列出字段查询：查询 id,userName ,salary字段的所有记录 -- 注意 select * from cms_ad 效率最低

select id, userName,salary from cms_ad;

-- 2. 给字段起别名 select id, userName as u,salary from cms_ad;

-- 3 表起别名（cms_ad as c）和字段来自哪个表名(表.字段) select id, c.userName ,c.salary from cms_ad as c;

[3月3号]

14.条件查询

--1. 列出字段查询：

查询 id,userName ,salary字段的所有记录
注意 select * from cms_ad 效率最低

select id, userName,salary from cms_ad;

--2.字段|表起别名

给字段起别名 [as] u select id, userName as u,salary from cms_ad;
表起别名（cms_ad as c）和字段来自哪个表名(表.字段) select id, c.userName ,c.salary from cms_ad as c;

--3.select表达式

后面也可以加函数 eg：select versions();

select 3+5,5*7;

mysql> select * from cms_ad;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | raaas    | 1000.00 | ds       |  20 |
|  2 | fhg      | 1000.00 | 12       |  22 |
|  3 | yg       | 1000.00 | 12455    |  20 |
|  4 | gg       | 1000.00 | hgf      |  20 |
+----+----------+---------+----------+-----+
4 rows in set (0.00 sec)

mysql> select * from cms_ad where id>2;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  3 | yg       | 1000.00 | 12455    |  20 |
|  4 | gg       | 1000.00 | hgf      |  20 |
+----+----------+---------+----------+-----+
2 rows in set (0.00 sec)

mysql> select * from cms_ad where id=3;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  3 | yg       | 1000.00 | 12455    |  20 |
+----+----------+---------+----------+-----+
1 row in set (0.00 sec)

--4.查询 cms_ad中编号除了3的记录(两种方法)

mysql> select * from cms_ad where id!=3;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | raaas    | 1000.00 | ds       |  20 |
|  2 | fhg      | 1000.00 | 12       |  22 |
|  4 | gg       | 1000.00 | hgf      |  20 |
+----+----------+---------+----------+-----+
3 rows in set (0.00 sec)

mysql> select * from cms_ad where id<>3;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | raaas    | 1000.00 | ds       |  20 |
|  2 | fhg      | 1000.00 | 12       |  22 |
|  4 | gg       | 1000.00 | hgf      |  20 |
+----+----------+---------+----------+-----+
3 rows in set (0.00 sec)

--5.<=>(判断null值)

select * from user where name <=>null;

select * from user where name is null;

--6.&&，AND逻辑与

--7.||，OR逻辑或

查询cms_ad中编号大于等于3并且小于等于6的记录(范围and)

mysql> select * from cms_ad where id>3 and id<6;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  4 | gg       | 1254.00 | hgf      |  20 |
|  5 | pp       | 8752.00 | 11       |  12 |
+----+----------+---------+----------+-----+
2 rows in set (0.00 sec)

多个并列条件查询
and（条件范围小，严格）

查询cms_ad中编号大于等于3并且年龄小于20的记录

mysql> select * from cms_ad where id>=3 and age<20;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  5 | pp       | 8752.00 | 11       |  12 |
+----+----------+---------+----------+-----+
1 row in set (0.00 sec)

or (条件范围大)

查询cms_ad中编号大于等于3或年龄小于20的记录

mysql> select * from cms_ad where id>=3 or age<20;
+----+----------+----------+----------+-----+
| id | userName | salary   | password | age |
+----+----------+----------+----------+-----+
|  3 | yg       |  5864.00 | 12455    |  20 |
|  4 | gg       |  1254.00 | hgf      |  20 |
|  5 | pp       |  8752.00 | 11       |  12 |
|  6 | uj       | 10000.00 | 42       |  28 |
|  7 | gf       | 12453.00 | 31       |  22 |
| 14 | tt       |  1000.00 | jjjk     |  20 |
+----+----------+----------+----------+-----+
6 rows in set (0.00 sec)

--8.[NOT] between....and

查询cms_ad中编号3-6的记录

mysql>  select * from cms_ad where id between 3 and 6;
+----+----------+----------+----------+-----+
| id | userName | salary   | password | age |
+----+----------+----------+----------+-----+
|  3 | yg       |  5864.00 | 12455    |  20 |
|  4 | gg       |  1254.00 | hgf      |  20 |
|  5 | pp       |  8752.00 | 11       |  12 |
|  6 | uj       | 10000.00 | 42       |  28 |
+----+----------+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> select * from cms_ad where id>=3 and id<=6;
+----+----------+----------+----------+-----+
| id | userName | salary   | password | age |
+----+----------+----------+----------+-----+
|  3 | yg       |  5864.00 | 12455    |  20 |
|  4 | gg       |  1254.00 | hgf      |  20 |
|  5 | pp       |  8752.00 | 11       |  12 |
|  6 | uj       | 10000.00 | 42       |  28 |
+----+----------+----------+----------+-----+
4 rows in set (0.00 sec)

查询cms_ad中编号不在3-6的记录

mysql>  select * from cms_ad where id not between 3 and 6;
+----+----------+-----------+----------+-----+
| id | userName | salary    | password | age |
+----+----------+-----------+----------+-----+
|  1 | raaas    | 858400.00 | ds       |  20 |
|  2 | fhg      |   2124.00 | 12       |  22 |
|  7 | gf       |  12453.00 | 31       |  22 |
| 14 | tt       |   1000.00 | jjjk     |  20 |
+----+----------+-----------+----------+-----+
4 rows in set (0.00 sec)

mysql> select * from cms_ad where id<3 or id>6;
+----+----------+-----------+----------+-----+
| id | userName | salary    | password | age |
+----+----------+-----------+----------+-----+
|  1 | raaas    | 858400.00 | ds       |  20 |
|  2 | fhg      |   2124.00 | 12       |  22 |
|  7 | gf       |  12453.00 | 31       |  22 |
| 14 | tt       |   1000.00 | jjjk     |  20 |
+----+----------+-----------+----------+-----+
4 rows in set (0.00 sec)

--9.[NOT] IN (值1,值2,..)指定值

查询cms_ad 中编号 3，5，6的记录

mysql> select * from cms_ad where id in(3,5,6);
+----+----------+----------+----------+-----+
| id | userName | salary   | password | age |
+----+----------+----------+----------+-----+
|  3 | yg       |  5864.00 | 12455    |  20 |
|  5 | pp       |  8752.00 | 11       |  12 |
|  6 | uj       | 10000.00 | 42       |  28 |
+----+----------+----------+----------+-----+
3 rows in set (0.00 sec)

mysql> select * from cms_ad where id=3 or id=5 or id=6;
+----+----------+----------+----------+-----+
| id | userName | salary   | password | age |
+----+----------+----------+----------+-----+
|  3 | yg       |  5864.00 | 12455    |  20 |
|  5 | pp       |  8752.00 | 11       |  12 |
|  6 | uj       | 10000.00 | 42       |  28 |
+----+----------+----------+----------+-----+
3 rows in set (0.00 sec)

查询cms_ad中编号不是3，5，6的记录

mysql> select * from cms_ad where id not in(3,5,6);
+----+----------+-----------+----------+-----+
| id | userName | salary    | password | age |
+----+----------+-----------+----------+-----+
|  1 | raaas    | 858400.00 | ds       |  20 |
|  2 | fhg      |   2124.00 | 12       |  22 |
|  4 | gg       |   1254.00 | hgf      |  20 |
|  7 | gf       |  12453.00 | 31       |  22 |
| 14 | tt       |   1000.00 | jjjk     |  20 |
+----+----------+-----------+----------+-----+
5 rows in set (0.00 sec)

mysql> select * from cms_ad where id<>3 and id<>5 and id<>6;
+----+----------+-----------+----------+-----+
| id | userName | salary    | password | age |
+----+----------+-----------+----------+-----+
|  1 | raaas    | 858400.00 | ds       |  20 |
|  2 | fhg      |   2124.00 | 12       |  22 |
|  4 | gg       |   1254.00 | hgf      |  20 |
|  7 | gf       |  12453.00 | 31       |  22 |
| 14 | tt       |   1000.00 | jjjk     |  20 |
+----+----------+-----------+----------+-----+
5 rows in set (0.00 sec)

--10.模糊查询

[NOT] LIKE string [ESCAPE string]
关键字：

%：代表0个或多个字符

_：代表一个字符
show variables like 'character_set_%';
查询cms_ad中userName中首字母是‘t'的记录

mysql> select * from cms_ad where userName like 't%';
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
| 14 | tt       | 1000.00 | jjjk     |  20 |
+----+----------+---------+----------+-----+
1 row in set (0.01 sec)

查询cms_ad中userName中包含‘t'的记录

mysql> select * from cms_ad where userName like '%t%';
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
| 14 | tt       | 1000.00 | jjjk     |  20 |
+----+----------+---------+----------+-----+
1 row in set (0.00 sec)s

查询cms_ad中userName中字符长度是3的记录

mysql> select * from cms_ad where userName like '___';
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  2 | fhg      | 2124.00 | 12       |  22 |
+----+----------+---------+----------+-----+
1 row in set (0.00 sec)

查询cms_ad中userName中第二个字符是‘g’的记录

mysql> select * from cms_ad where userName like '_g%';
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  3 | yg       | 5864.00 | 12455    |  20 |
|  4 | gg       | 1254.00 | hgf      |  20 |
+----+----------+---------+----------+-----+
2 rows in set (0.00 sec)

查询cms_ad中userName中第二个字符是‘_’的记录

mysql> select * from cms_ad where userName like '_#_%'escape '#';//#符号可以替换
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  8 | y_g      | 1000.00 |          |  20 |
+----+----------+---------+----------+-----+
1 row in set (0.00 sec)

mysql> select * from cms_ad where userName like '_E_%'escape 'E';
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  8 | y_g      | 1000.00 |          |  20 |
+----+----------+---------+----------+-----+
1 row in set (0.00 sec)

查询cms_ad中userName中第二个字符是‘o’并且编号大于5的记录

mysql> select * from cms_ad where userName like '_o%' and id>=5;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  5 | po       | 8752.00 | 11       |  12 |
| 11 | koad     | 1564.00 | asdw     |  29 |
| 15 | to       | 5242.00 | 452ad    |  20 |
+----+----------+---------+----------+-----+
3 rows in set (0.00 sec)

查询cms_ad中userName中2-7的记录，也可以是年龄大于20的记录

mysql> select * from cms_ad where id between 2 and 7 or age>=20;
+----+----------+-----------+----------+-----+
| id | userName | salary    | password | age |
+----+----------+-----------+----------+-----+
|  1 | raaas    | 858400.00 | ds       |  20 |
|  2 | fhg      |   2124.00 | 12       |  22 |
|  3 | yg       |   5864.00 | 12455    |  20 |
|  4 | gg       |   1254.00 | hgf      |  20 |
|  5 | po       |   8752.00 | 11       |  12 |
|  6 | uj       |  10000.00 | 42       |  28 |
|  7 | gf       |  12453.00 | 31       |  22 |
|  8 | y_g      |   1000.00 |          |  20 |
| 11 | koad     |   1564.00 | asdw     |  29 |
| 14 | tt       |   1000.00 | jjjk     |  20 |
| 15 | to       |   5242.00 | 452ad    |  20 |
+----+----------+-----------+----------+-----+
11 rows in set (0.00 sec)
//加括号的作用就是为了确定优先级
mysql> select * from cms_ad where (id>=2 and id<=7) or age>=20;
+----+----------+-----------+----------+-----+
| id | userName | salary    | password | age |
+----+----------+-----------+----------+-----+
|  1 | raaas    | 858400.00 | ds       |  20 |
|  2 | fhg      |   2124.00 | 12       |  22 |
|  3 | yg       |   5864.00 | 12455    |  20 |
|  4 | gg       |   1254.00 | hgf      |  20 |
|  5 | po       |   8752.00 | 11       |  12 |
|  6 | uj       |  10000.00 | 42       |  28 |
|  7 | gf       |  12453.00 | 31       |  22 |
|  8 | y_g      |   1000.00 |          |  20 |
| 11 | koad     |   1564.00 | asdw     |  29 |
| 14 | tt       |   1000.00 | jjjk     |  20 |
| 15 | to       |   5242.00 | 452ad    |  20 |
+----+----------+-----------+----------+-----+
11 rows in set (0.00 sec)

--11.[GROUP BY 字段名称]:

将某个字段中的相同值分为一组实现分组效果，每组中形同的值只显示一个值
显示的字段只能是分组的那个字段和聚合函数的显示（重要）
查询cms_ad表中，对age字段分组并且显示每组的人数，薪水的最大值，最小值，平均值，薪水的和；

mysql> select age,count(*),max(salary),min(salary),sum(salary)
    -> from cms_ad
    -> group by age;
+-----+----------+-------------+-------------+-------------+
| age | count(*) | max(salary) | min(salary) | sum(salary) |
+-----+----------+-------------+-------------+-------------+
|  20 |        6 |   858400.00 |     1000.00 |   872760.00 |
|  22 |        2 |    12453.00 |     2124.00 |    14577.00 |
|  12 |        1 |     8752.00 |     8752.00 |     8752.00 |
|  28 |        1 |    10000.00 |    10000.00 |    10000.00 |
|  29 |        1 |     1564.00 |     1564.00 |     1564.00 |
+-----+----------+-------------+-------------+-------------+
5 rows in set (0.00 sec)
//另一种写法将名字重新命名后显示
mysql> select age,count(*),max(salary) as max,min(salary) as min,sum(salary) as sum
    -> from cms_ad
    -> group by age;
+-----+----------+-----------+----------+-----------+
| age | count(*) | max       | min      | sum       |
+-----+----------+-----------+----------+-----------+
|  20 |        6 | 858400.00 |  1000.00 | 872760.00 |
|  22 |        2 |  12453.00 |  2124.00 |  14577.00 |
|  12 |        1 |   8752.00 |  8752.00 |   8752.00 |
|  28 |        1 |  10000.00 | 10000.00 |  10000.00 |
|  29 |        1 |   1564.00 |  1564.00 |   1564.00 |
+-----+----------+-----------+----------+-----------+
5 rows in set (0.00 sec)

--12.[HAving 条件]：对分组后的信息进行二次过滤

说明：[HAVING 条件]必须和GROUP BY一起使用
查询cms_ad表中，对age字段分组并且显示年龄，每组的人数，薪水的最大值，最小值，平均值，薪水的和并且显示人数大于等于2的记录；

mysql> select age,count(*),max(salary) as max,min(salary) as min,avg(salary),sum(salary) as sum
    -> from cms_ad
    -> group by age
    -> having count(*)>2;
+-----+----------+-----------+---------+---------------+-----------+
| age | count(*) | max       | min     | avg(salary)   | sum       |
+-----+----------+-----------+---------+---------------+-----------+
|  20 |        6 | 858400.00 | 1000.00 | 145460.000000 | 872760.00 |
+-----+----------+-----------+---------+---------------+-----------+
1 row in set (0.00 sec)

查询cms_ad表中，首先查找2-7记录然后对age字段分组并且显示年龄，每组的人数，薪水的最大值，最小值，平均值，薪水的和并且显示人数大于等于2的记录；

mysql> select age,count(*),max(salary) as max,min(salary) as min,avg(salary),sum(salary) as sum
    -> from cms_ad
    -> where id between 2 and 7
    -> group by age
    -> having count(*)>=2;
+-----+----------+----------+---------+-------------+----------+
| age | count(*) | max      | min     | avg(salary) | sum      |
+-----+----------+----------+---------+-------------+----------+
|  22 |        2 | 12453.00 | 2124.00 | 7288.500000 | 14577.00 |
|  20 |        2 |  5864.00 | 1254.00 | 3559.000000 |  7118.00 |
+-----+----------+----------+---------+-------------+----------+
2 rows in set (0.00 sec)

--13.[ORDER BY 字段名称]：排序

order by 字段名称
[ASC 升序|DESC 降序]

查询所有员工编号并降序显示

//对一个字段排序
mysql> use employee; 
mysql> select * from employee order by department_id desc;
+----+--------+----------+-----+-----+---------------+---------+--------+
| id | job_id | name     | sex | age | department_id | salary  | bonus  |
+----+--------+----------+-----+-----+---------------+---------+--------+
|  5 |      5 | yangyang |   1 |  22 |           103 | 9000.00 | 100.00 |
|  8 |      8 | ji       |   0 |  27 |           103 | 6120.00 | 200.00 |
|  4 |      4 | wang     |   1 |  22 |           102 | 3000.00 | 100.00 |
|  6 |      6 | zhang    |   0 |  27 |           102 | 7000.00 |   0.00 |
|  9 |      9 | zhaosi   |   0 |  27 |           102 | 6520.00 | 100.00 |
| 10 |     10 | ki       |   0 |  18 |           102 | 8000.00 |   0.00 |
|  2 |      2 | zhaoliu  |   0 |  27 |           101 | 8000.00 | 250.00 |
|  7 |      7 | lier     |   0 |  27 |           101 | 5000.00 | 100.00 |
|  1 |      1 | lisi     |   0 |  18 |           100 | 3200.00 |   0.00 |
|  3 |      3 | liuba    |   1 |  22 |           100 | 2500.00 | 200.00 |
+----+--------+----------+-----+-----+---------------+---------+--------+
10 rows in set (0.00 sec)

//对两个字段排序
mysql> select * from employee order by department_id desc,id desc;
+----+--------+----------+-----+-----+---------------+---------+--------+
| id | job_id | name     | sex | age | department_id | salary  | bonus  |
+----+--------+----------+-----+-----+---------------+---------+--------+
|  8 |      8 | ji       |   0 |  27 |           103 | 6120.00 | 200.00 |
|  5 |      5 | yangyang |   1 |  22 |           103 | 9000.00 | 100.00 |
| 10 |     10 | ki       |   0 |  18 |           102 | 8000.00 |   0.00 |
|  9 |      9 | zhaosi   |   0 |  27 |           102 | 6520.00 | 100.00 |
|  6 |      6 | zhang    |   0 |  27 |           102 | 7000.00 |   0.00 |
|  4 |      4 | wang     |   1 |  22 |           102 | 3000.00 | 100.00 |
|  7 |      7 | lier     |   0 |  27 |           101 | 5000.00 | 100.00 |
|  2 |      2 | zhaoliu  |   0 |  27 |           101 | 8000.00 | 250.00 |
|  3 |      3 | liuba    |   1 |  22 |           100 | 2500.00 | 200.00 |
|  1 |      1 | lisi     |   0 |  18 |           100 | 3200.00 |   0.00 |
+----+--------+----------+-----+-----+---------------+---------+--------+
10 rows in set (0.00 sec)

+----+--------+----------+-----+-----+---------------+---------+--------+
//以下为练习
//1.查询工种joub_id员工的工资最大值，平均值，总和，并按job_id降序
mysql> select job_id,max(salary),avg(salary),sum(salary)
    -> from employee
    -> group by job_id
    -> order by job_id desc;
+--------+-------------+-------------+-------------+
| job_id | max(salary) | avg(salary) | sum(salary) |
+--------+-------------+-------------+-------------+
|     10 |     8000.00 | 8000.000000 |     8000.00 |
|      9 |     6520.00 | 6520.000000 |     6520.00 |
|      8 |     6120.00 | 6120.000000 |     6120.00 |
|      7 |     5000.00 | 5000.000000 |     5000.00 |
|      6 |     7000.00 | 7000.000000 |     7000.00 |
|      5 |     9000.00 | 9000.000000 |     9000.00 |
|      4 |     3000.00 | 3000.000000 |     3000.00 |
|      3 |     2500.00 | 2500.000000 |     2500.00 |
|      2 |     8000.00 | 8000.000000 |     8000.00 |
|      1 |     3200.00 | 3200.000000 |     3200.00 |
+--------+-------------+-------------+-------------+
10 rows in set (0.00 sec)

//2.查询所有员工部门编号，员工数量和工资的平均值，并按平均工资降序
mysql> select department_id,count(*),avg(salary) as a
    -> from employee
    -> group by department_id
    -> order by a desc;
+---------------+----------+-------------+
| department_id | count(*) | a           |
+---------------+----------+-------------+
|           103 |        2 | 7560.000000 |
|           101 |        2 | 6500.000000 |
|           102 |        4 | 6130.000000 |
|           100 |        2 | 2850.000000 |
+---------------+----------+-------------+
4 rows in set (0.00 sec)

//3.查询员工的名称和部门编号和年薪，按年薪降序并按照姓名升序
mysql> select name,department_id,(salary+bonus)*12 as yearSalary
    -> from employee
    -> order by yearSalary desc,name asc;
+----------+---------------+------------+
| name     | department_id | yearSalary |
+----------+---------------+------------+
| yangyang |           103 |  109200.00 |
| zhaoliu  |           101 |   99000.00 |
| ki       |           102 |   96000.00 |
| zhang    |           102 |   84000.00 |
| zhaosi   |           102 |   79440.00 |
| ji       |           103 |   75840.00 |
| lier     |           101 |   61200.00 |
| lisi     |           100 |   38400.00 |
| wang     |           102 |   37200.00 |
| liuba    |           100 |   32400.00 |
+----------+---------------+------------+
10 rows in set (0.00 sec)

//4.查询部门编号为101的员工个数
mysql> select count(*)from employee where department_id=101;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

--14.[LIMIT OFFSET,LENGTH] 记录的截取

OFFSET：偏移量，起始编号，编号从0开始
LENGTH：显示的条数（记录数）
作用：实现web页面的分页效果，计算每页中的起始编号

OFFSET：($curpage-1)*$pagesize (当前页-1)*显示的条数

假设 numberperpage 表示每页要显示的条数，pagenumber表示页码，那么返回第pagenumber页，每页条数为numberperpage的sql语句：(切记将数算出来）

代码示例: eg：select * from studnet limit (pagenumber-1)*numberperpage,numberperpage eg：select * from student limit numberperpage offset (pagenumber-1)*numberperpage

mysql> select *from employee;
+----+--------+----------+-----+-----+---------------+---------+--------+
| id | job_id | name     | sex | age | department_id | salary  | bonus  |
+----+--------+----------+-----+-----+---------------+---------+--------+
|  1 |      1 | lisi     |   0 |  18 |           100 | 3200.00 |   0.00 |
|  2 |      2 | zhaoliu  |   0 |  27 |           101 | 8000.00 | 250.00 |
|  3 |      3 | liuba    |   1 |  22 |           100 | 2500.00 | 200.00 |
|  4 |      4 | wang     |   1 |  22 |           102 | 3000.00 | 100.00 |
|  5 |      5 | yangyang |   1 |  22 |           103 | 9000.00 | 100.00 |
|  6 |      6 | zhang    |   0 |  27 |           102 | 7000.00 |   0.00 |
|  7 |      7 | lier     |   0 |  27 |           101 | 5000.00 | 100.00 |
|  8 |      8 | ji       |   0 |  27 |           103 | 6120.00 | 200.00 |
|  9 |      9 | zhaosi   |   0 |  27 |           102 | 6520.00 | 100.00 |
| 10 |     10 | ki       |   0 |  18 |           102 | 8000.00 |   0.00 |
+----+--------+----------+-----+-----+---------------+---------+--------+
10 rows in set (0.00 sec)

//查询employee的前五条记录
mysql> select * from employee where id<5;    //不准确 必须是连续的id才准确
+----+--------+---------+-----+-----+---------------+---------+--------+
| id | job_id | name    | sex | age | department_id | salary  | bonus  |
+----+--------+---------+-----+-----+---------------+---------+--------+
|  1 |      1 | lisi    |   0 |  18 |           100 | 3200.00 |   0.00 |
|  2 |      2 | zhaoliu |   0 |  27 |           101 | 8000.00 | 250.00 |
|  3 |      3 | liuba   |   1 |  22 |           100 | 2500.00 | 200.00 |
|  4 |      4 | wang    |   1 |  22 |           102 | 3000.00 | 100.00 |
+----+--------+---------+-----+-----+---------------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from employee limit 0,5; //能精确查找前五条的编号
+----+--------+----------+-----+-----+---------------+---------+--------+
| id | job_id | name     | sex | age | department_id | salary  | bonus  |
+----+--------+----------+-----+-----+---------------+---------+--------+
|  1 |      1 | lisi     |   0 |  18 |           100 | 3200.00 |   0.00 |
|  2 |      2 | zhaoliu  |   0 |  27 |           101 | 8000.00 | 250.00 |
|  3 |      3 | liuba    |   1 |  22 |           100 | 2500.00 | 200.00 |
|  4 |      4 | wang     |   1 |  22 |           102 | 3000.00 | 100.00 |
|  5 |      5 | yangyang |   1 |  22 |           103 | 9000.00 | 100.00 |
+----+--------+----------+-----+-----+---------------+---------+--------+
5 rows in set (0.00 sec)

/*2表示返回2行，5表示从表的第6行开始*/
mysql> select * from employee limit 2 offset 5;
+----+--------+-------+-----+-----+---------------+---------+--------+
| id | job_id | name  | sex | age | department_id | salary  | bonus  |
+----+--------+-------+-----+-----+---------------+---------+--------+
|  6 |      6 | zhang |   0 |  27 |           102 | 7000.00 |   0.00 |
|  7 |      7 | l_ier |   0 |  27 |           101 | 5000.00 | 100.00 |
+----+--------+-------+-----+-----+---------------+---------+--------+
2 rows in set (0.00 sec)

//查找最新的数据的前五条并降序显示
mysql> select * from employee order by id desc limit 0,5;
+----+--------+--------+-----+-----+---------------+---------+--------+
| id | job_id | name   | sex | age | department_id | salary  | bonus  |
+----+--------+--------+-----+-----+---------------+---------+--------+
| 10 |     10 | ki     |   0 |  18 |           102 | 8000.00 |   0.00 |
|  9 |      9 | zhaosi |   0 |  27 |           102 | 6520.00 | 100.00 |
|  8 |      8 | ji     |   0 |  27 |           103 | 6120.00 | 200.00 |
|  7 |      7 | lier   |   0 |  27 |           101 | 5000.00 | 100.00 |
|  6 |      6 | zhang  |   0 |  27 |           102 | 7000.00 |   0.00 |
+----+--------+--------+-----+-----+---------------+---------+--------+
5 rows in set (0.00 sec)

//查询employee的第一页数据，每页显示3条记录
mysql> select * from employee limit 0,3;
+----+--------+---------+-----+-----+---------------+---------+--------+
| id | job_id | name    | sex | age | department_id | salary  | bonus  |
+----+--------+---------+-----+-----+---------------+---------+--------+
|  1 |      1 | lisi    |   0 |  18 |           100 | 3200.00 |   0.00 |
|  2 |      2 | zhaoliu |   0 |  27 |           101 | 8000.00 | 250.00 |
|  3 |      3 | liuba   |   1 |  22 |           100 | 2500.00 | 200.00 |
+----+--------+---------+-----+-----+---------------+---------+--------+
3 rows in set (0.00 sec)

//查询employee的第二页数据，每页显示3条记录
mysql> select * from employee limit 3,3;
+----+--------+----------+-----+-----+---------------+---------+--------+
| id | job_id | name     | sex | age | department_id | salary  | bonus  |
+----+--------+----------+-----+-----+---------------+---------+--------+
|  4 |      4 | wang     |   1 |  22 |           102 | 3000.00 | 100.00 |
|  5 |      5 | yangyang |   1 |  22 |           103 | 9000.00 | 100.00 |
|  6 |      6 | zhang    |   0 |  27 |           102 | 7000.00 |   0.00 |
+----+--------+----------+-----+-----+---------------+---------+--------+
3 rows in set (0.00 sec)

//查询employee的第三页数据，每页显示3条记录
mysql> select * from employee limit 6,3;
+----+--------+--------+-----+-----+---------------+---------+--------+
| id | job_id | name   | sex | age | department_id | salary  | bonus  |
+----+--------+--------+-----+-----+---------------+---------+--------+
|  7 |      7 | lier   |   0 |  27 |           101 | 5000.00 | 100.00 |
|  8 |      8 | ji     |   0 |  27 |           103 | 6120.00 | 200.00 |
|  9 |      9 | zhaosi |   0 |  27 |           102 | 6520.00 | 100.00 |
+----+--------+--------+-----+-----+---------------+---------+--------+
3 rows in set (0.00 sec)

15.多表查询

老方法：select ... from 表1,表二 where 条件 (该方法目前不推荐)

--1.多表连接查询

SELECT 表.字段名称,表.字段名称... FROM 表1 连接类型表2 ON 条件连接类型表3 ON 条件... 连接类型：

(1)内连接: [INNER] JOIN

查找符合条件的信息

条件分类：等值 (常用) | 非等值 | 自连接(菜单级联)

/*菜单联级查询*/
mysql> select * from cate;
+----+----------+-----+
| id | name     | pid |
+----+----------+-----+
|  1 | 家用电器 |   0 |
|  2 | 手机     |   0 |
|  3 | 电脑     |   0 |
|  4 | 生活电器 |   1 |
|  5 | 厨房电器 |   1 |
|  6 | 加湿器   |   4 |
|  7 | 饮水机   |   4 |
|  8 | 高压锅   |   5 |
|  9 | 面包机   |   5 |
+----+----------+-----+
9 rows in set (0.00 sec)

/*查询所有子菜单（cute）及对应的父菜单（cate）的名称*/
mysql> select s.id,s.name as 子菜单,p.name as 父菜单
    -> from cate as p
    -> inner join cate as s
    -> on s.pid=p.id;
+----+----------+----------+
| id | 子菜单   | 父菜单   |
+----+----------+----------+
|  4 | 生活电器 | 家用电器 |
|  5 | 厨房电器 | 家用电器 |
|  6 | 加湿器   | 生活电器 |
|  7 | 饮水机   | 生活电器 |
|  8 | 高压锅   | 厨房电器 |
|  9 | 面包机   | 厨房电器 |
+----+----------+----------+
6 rows in set (0.00 sec)

特点：①添加排序、分组、筛选 ②inner可以省略 ③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读 ④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

/*查询 员工名（在employee），部门名（在department表中） 两表联合*/
mysql> select e.name,d.department_name
    -> from employee as e
    -> inner join department as d
    -> on e.department_id=d.department_id;
+----------+-----------------+
| name     | department_name |
+----------+-----------------+
| lisi     | 部门1           |
| zhaoliu  | 部门2           |
| liuba    | 部门1           |
| wang     | 部门3           |
| yangyang | 部门4           |
| zhang    | 部门3           |
| lier     | 部门2           |
| ji       | 部门4           |
| zhaosi   | 部门3           |
| ki       | 部门3           |
+----------+-----------------+
10 rows in set (0.00 sec)

/*查询员工名，部门名，工种名（三表联合查询）*/
mysql> select e.name,d.department_name,j.job_name
    -> from employee as e
    -> inner join department as d on e.department_id=d.department_id
    -> inner join job as j on e.job_id=j.job_id;
+---------+-----------------+----------+
| name    | department_name | job_name |
+---------+-----------------+----------+
| lisi    | 部门1           | 汽修     |
| zhaoliu | 部门2           | 电器     |
| liuba   | 部门1           | 瓦工     |
| wang    | 部门3           | 刷漆     |
+---------+-----------------+----------+
4 rows in set (0.00 s

/* 查询那个工种的员工人数大于2的工种名和员工数 */
mysql> select j.job_name,count(*) as c
    -> from employee as e
    -> inner join job as j
    -> on e.job_id=j.job_id
    -> group by j.job_name
    -> having c>0;
+----------+---+
| job_name | c |
+----------+---+
| 汽修     | 1 |
| 电器     | 1 |
| 瓦工     | 1 |
| 刷漆     | 1 |
+----------+---+
 
/*查询部门名为（department表）为：（部门1，部门2）的员工信息（employee表）*/
mysql> select d.department_name,e.*
    -> from employee as e
    -> inner join department as d
    -> on e.department_id=d.department_id 
    -> where d.department_name in ('部门1','部门2');
+-----------------+----+--------+---------+-----+-----+---------------+---------+--------+
| department_name | id | job_id | name    | sex | age | department_id | salary  | bonus  |
+-----------------+----+--------+---------+-----+-----+---------------+---------+--------+
| 部门1           |  1 |      1 | lisi    |   0 |  18 |           100 | 3200.00 |   0.00 |
| 部门2           |  2 |      2 | zhaoliu |   0 |  27 |           101 | 8000.00 | 250.00 |
| 部门1           |  3 |      3 | liuba   |   1 |  22 |           100 | 2500.00 | 200.00 |
| 部门2           |  7 |      7 | l_ier   |   0 |  27 |           101 | 5000.00 | 100.00 |
+-----------------+----+--------+---------+-----+-----+---------------+---------+--------+
4 rows in set (0.00 sec)

(2)外连接：

1.外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 外连接查询结果=内连接结果+主表中有而从表没有的记录

2.左外连接，left join左边的是主表

是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null

右外连接，right join右边的是主表

是以右表的数据去匹配左表，所以左外连接能做到的查询，右外连接也能做到

3.左外和右外交换两个表的顺序，可以实现同样的效果 4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

/*查询所有部门名称及员工信息*/
#先用内连接
mysql> select d.department_name,e.name
    -> from department as d
    -> inner join employee as e
    -> on d.department_id=e.department_id;
+-----------------+----------+
| department_name | name     |
+-----------------+----------+
| 部门1           | lisi     |
| 部门2           | zhaoliu  |
| 部门1           | liuba    |
| 部门3           | wang     |
| 部门4           | yangyang |
| 部门3           | zhang    |
| 部门2           | l_ier    |
| 部门4           | ji       |
| 部门3           | zhaosi   |
| 部门3           | ki       |
+-----------------+----------+

#左外连接
mysql> select d.department_name,e.name
    -> from department as d
    -> left join employee as e
    -> on d.department_id=e.department_id;
+-----------------+----------+
| department_name | name     |
+-----------------+----------+
| 部门1           | lisi     |
| 部门1           | liuba    |
| 部门2           | zhaoliu  |
| 部门2           | l_ier    |
| 部门3           | wang     |
| 部门3           | zhang    |
| 部门3           | zhaosi   |
| 部门3           | ki       |
| 部门4           | yangyang |
| 部门4           | ji       |
+-----------------+----------+

#右外连接
mysql> select d.department_name,e.name
    -> from employee as e
    -> right join department as d
    -> on d.department_id=e.department_id;
+-----------------+----------+
| department_name | name     |
+-----------------+----------+
| 部门1           | lisi     |
| 部门1           | liuba    |
| 部门2           | zhaoliu  |
| 部门2           | l_ier    |
| 部门3           | wang     |
| 部门3           | zhang    |
| 部门3           | zhaosi   |
| 部门3           | ki       |
| 部门4           | yangyang |
| 部门4           | ji       |
+-----------------+----------+
10 rows in set (0.00 sec)

16.MySQL数据类型

--1.标准SQL中的数值类型：

严格数值类型：INTEGER、SMALLINT、DECIMAL和NUMERIC

近似数值类型：FLOAT、REAL、DOUBLE和PRECISIO

扩展后增加的类型：TINYINT、MEDIUMINT、BIGINT、BIT

注意：INT是INTEGER的同名词，DEC是DECIMAL的同名词。

--2数据类型表汇总

以上 ↑ M 表示长度，即输入的数据长度，如1，表示是一个个位数 2，表示一个两位数

浮点型单精度float, 双精度double (不要对浮点数进行比较，近似值)；
float(M,D)，double(M,D) decimal(M,D): M表示数的总长度（当长度小于指定长度后自动补0），D表示小数位数。其中小数可以超位数，因为四舍五入，但是整数部分不能超！
---ZEROFILL:填补0，结合数字长度使用 --当添加的数字长度小于指定长度填补0 -- 自动添加UNSIGNED.

create table t4( num1 tinyint unsigned, num2 tinyint zerofill );
show warnings 可以查看错误原因

--3.字符串类型:

1. CHAR[(M)]:定长字符，范围至多255字节(一个字母占一个字节) 效率高不省空间

2. VARCHAR[(M)]:变长字符，范围至多65535字节效率低省空间

eg: 'hello' ：varchar(30) 实际存5字节； char(30) 实际30字节  eg: 加密的32位密码， char(32) 效率高

VARCHAR[(M)]范围受一下条件影响：  (1).范围至多65535字节  (2).gbk：至多2字节，65532/2  uf8：至多3字节，65535/3=21844  (3).字段中的所有CHAR, VARCHAR总长度不超过21844

create table t7( str1 char(8), str2 varchar(21836)  );

-- 所有一般 varchar(255)

3.TEXT：至多65535字节, 例如，文章内容一般用 text ,一般不会对文章内容查询 --TINYTEXT：至多255字节 -- TEXT：至多65535字节 --MEDIUMTEXT：16777215字节 --LONGTEXT：4294967295字节

4.BLOB ：最大65k, 大文件,二进制，存图像，视频 (实际存图像和视频的路径，特殊使用)

1024字节 = 1k

--4.枚举类型: enum 最多能存65535个值

--enum('男','女')

create table t8( sex enum('男','女') );

insert t8 (sex) values -- ('其他'); ('男'), ('女');

-- 5. 时间日期类型

-- year 年 1字节 -- time 时间 3字节 -- date 日期 4字节 -- datetime 时间日期 8字节（没有时间限制） -- timestamp 时间戳 4字节（有年限: 1970-2038年的秒-- 停止2147483647秒; 时区） -- bigint 整型存时间戳(秒)，将来在应用中用系统函数转为时间日期

select now(); -- 显示当前的系统时间 2021-03-10 10:55:53

mysql> use mytest;
Database changed
mysql> create table t9(  
	-> d1 year,  
	-> d2 time,  
	-> d3 date,  
	-> d4 datetime,  
	-> d5 timestamp);
Query OK, 0 rows affected (0.12 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-10 13:21:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert t9 values(now(),now(),now(),now(),now());
Query OK, 1 row affect+ed, 1 warning (0.01 sec)

mysql> select *  from t9;
+------+----------+------------+---------------------+---------------------+
| d1   | d2       | d3         | d4                  | d5                  |
+------+----------+------------+---------------------+---------------------+
| 2021 | 13:21:32 | 2021-03-10 | 2021-03-10 13:21:32 | 2021-03-10 13:21:32 |
+------+----------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert t9 values('2030','08:00:50','2030-10-01','2030-10-01 08:00:50','20301001080050');
Query OK, 1 row affected (0.01 sec)

mysql> select *  from t9;
+------+----------+------------+---------------------+---------------------+
| d1   | d2       | d3         | d4                  | d5                  |
+------+----------+------------+---------------------+---------------------+
| 2021 | 13:21:32 | 2021-03-10 | 2021-03-10 13:21:32 | 2021-03-10 13:21:32 |
| 2030 | 08:00:50 | 2030-10-01 | 2030-10-01 08:00:50 | 2030-10-01 08:00:50 |
+------+----------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

--6.布尔类型 tinyint(1) 可以存 0 ,1

-- 7.MySQL函数 select version();

1>. 字符串函数

(1) concat(n1,n2,....) 字符串连接

select concat('a','b','c');

employee 表将 name和age 的值连接产生新字段 nn, 并查询所有记录 select name,age, concat(name,age) as nn from employee;

(2) lower()转小写, upper() 转大写

select lower('Hello'),upper('Hello');

(3) trim()：去掉行头和行尾的空格 select trim(' A B '); select concat('#',trim(' A B '),'#');

(4) substring(str,x,y); 截取字符串， str:源字符串, x 开始的数字，y截取的长度

select substring('hello',1,3); -- hel

select substring('hello',3,3); --llo

-- ello 从第二个字符到后面所有的字符串 select substring('hello',2);

(5)replace(str,x,y);

str：源字符串 x：源子字符串 y：目标的子字符串

select replace('hello','e','t');

select replace('hello','e','t'); //比较特殊，将相同的字符都替换掉

mysql> select replace('hello','e','t');
+--------------------------+
| replace('hello','e','t') |
+--------------------------+
| htllo                    |
+--------------------------+
1 row in set (0.00 sec)

mysql> select replace('hello','e','t');
+--------------------------+
| replace('hello','e','t') |
+--------------------------+
| htllo                    |
+--------------------------+
1 row in set (0.00 sec)

2>.常用函数

abs(x)返回x的绝对值
ceil(x)向上取整
floor(x)向下取整
mod(x)返回x/y的模
round(x)返回参数x的四舍五入的有y位小数的值

mysql> select abs(-8),ceil(7.01),floor(5.99),8%3,mod(8,3),round(4.5),round(4.5678,2);
+---------+------------+-------------+------+----------+------------+-----------------+
| abs(-8) | ceil(7.01) | floor(5.99) | 8%3  | mod(8,3) | round(4.5) | round(4.5678,2) |
+---------+------------+-------------+------+----------+------------+-----------------+
|       8 |          8 |           5 |    2 |        2 |          5 |            4.57 |
+---------+------------+-------------+------+----------+------------+-----------------+
1 row in set (0.00 sec)

rand()返回（0~1）内随机数

mysql> select rand(),rand()*100,round(rand()*100);
+------------------------+------------------+-------------------+
| rand()                 | rand()*100       | round(rand()*100) |
+------------------------+------------------+-------------------+
| 0.00027419906135108234 | 38.3477986216059 |                92 |
+------------------------+------------------+-------------------+
1 row in set (0.00 sec)

3>.时间日期函数

curdate()返回当前日期
curtime()返回当前时间
now()返回当前的日期和时间

mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2021-03-15 | 16:27:25  | 2021-03-15 16:27:25 |
+------------+-----------+---------------------+
1 row in set (0.00 sec)

unix_timestamp(adte) 返回日期date的unix时间戳
from_unixtime返回unix时间戳的日期值
unix 时间戳：1970-01-01 00:00:00 - 2038-01-19 11:14:07的秒，也就是2147483647

mysql> select unix_timestamp('2021-03-15'),unix_timestamp(now());
+------------------------------+-----------------------+
| unix_timestamp('2021-03-15') | unix_timestamp(now()) |
+------------------------------+-----------------------+
|                   1615737600 |            1615797248 |
+------------------------------+-----------------------+
1 row in set (0.00 sec)

#二者相互转换

mysql> select from_unixtime(1615737600),from_unixtime(2147483647);
+---------------------------+---------------------------+
| from_unixtime(1615737600) | from_unixtime(2147483647) |
+---------------------------+---------------------------+
| 2021-03-15 00:00:00       | 2038-01-19 11:14:07       |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

week(date)返回日期date为一年中的第几周
year(date)返回日期date的年份
hour(time)返回time的小时值
minute(time)返回time的分钟值
monthname(date)返回日期date的月份

mysql> select week('2021-03-15 16:41:20');
+-----------------------------+
| week('2021-03-15 16:41:20') |
+-----------------------------+
|                          11 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select year('2021-03-15 16:41:20');
+-----------------------------+
| year('2021-03-15 16:41:20') |
+-----------------------------+
|                        2021 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select hour('2021-03-15 16:41:20');
+-----------------------------+
| hour('2021-03-15 16:41:20') |
+-----------------------------+
|                          16 |
+-----------------------------+
1 row in set (0.00 sec)

date_format(date,fmt)返回按字符串fmt格式化日期date的值
date_add(date,interval expr type)返回一个日期或时间值加上一个时间间隔的时间值 select date_add('2021-03-15 16:50:20',interval 3 day);
datediff(expr,expr2)返回起始时间expr和结束时间expr2之间的天数

mysql> select DATE_FORMAT(NOW(),'%m-%d-%Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%m-%d-%Y') |
+-------------------------------+
| 03-15-2021                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2021-03-15 16:50:20',interval 3 day);
+------------------------------------------------+
| date_add('2021-03-15 16:50:20',interval 3 day) |
+------------------------------------------------+
| 2021-03-18 16:50:20                            |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2021-05-01','2021-03-15');
+-------------------------------------+
| datediff('2021-05-01','2021-03-15') |
+-------------------------------------+
|                                  47 |
+-------------------------------------+
1 row in set (0.00 sec)

4>.流程函数

if(value,t,f)如果value是真，返回t；否则返回f

mysql> select if(1=1,'ok','error');
+----------------------+
| if(1=1,'ok','error') |
+----------------------+
| ok                   |
+----------------------+
1 row in set (0.00 sec)

ifnull(value1,value2)如果value1不为空，返回value1,否则value2

mysql> select ifnull(null,0), ifnull(200,0);
+----------------+---------------+
| ifnull(null,0) | ifnull(200,0) |
+----------------+---------------+
|              0 |           200 |
+----------------+---------------+
1 row in set (0.00 sec)

5>.其他函数

--DATABASE()返回当前数据库名 select database();

--VERSION()返回当前数据库版本 select version(); --USER()返回当前登录用户名 select user();

--INET_ATON(IP)返回IP地址的数字表示 --INET_NTOA(num)返回数字代表的IP地址

mysql> select inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(2130706433);
+-----------------------+
| inet_ntoa(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

--PASSWORD(str)返回字符串str的加密版本（Mysql8.0以上不支持） --MD5()返回字符串str的MD5值 -- 32位加密

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

17.约束

--1.注意：

primary key ,unique,foreign key 外键可以写在表约束的

not null,default 只能列约束

--2.约束分类：

列约束：在字段后面实现约束（不能创建两个主键）
```
create table m(
	num1 int primary key,
	num2,int
);
```

表约束写法：（此写法可以创建两个主键）

create table m1(
	num1 int,
	num2,int,
	primary key(num1,num2)
);

--3.外键

1.实现对两个表的字段完整性和一致性约束

2.关键字：foreign key 字段外键 references 参考表（对应字段）

先有参考表

  create table depart1(
  	id int(3) zerofill primary key auto_increment,
      dname varchar(30) not null
  );
  
  insert depart1(id,dname) values
     (1,'部门1'),
     (2,'部门2'),
     (3,'部门3');
  
  #创建子表
  create table employ1(
  	id int unsigned primary key auto_increment,
     	name varchar(30) not null,
      depart_id int(3) zerofill not null,
      foreign key(depart_id) references depart1(id)
  );
  
  insert employ1(name,depart_id) values
  	('zhangsan',1),
  	('lisi1',2),
  	('alice',2);
  --('tom',4)会报错，原因是子表添加的外键值必须在参考表中有对应的值

--参考不能更新或删除，子表中已经使用的外键值 -- 更新参考表 id 是 02 的改 6 update depart1 set id=6 where id=02;

-- 删除参考表 id 是 01 delete from depart1 where id=01;

-- 结论:

-- a.MYSQL引擎为 InnoDB (show create table employ1;) -- alter table employ1 engine=InnoDB; -- b.数据类型必须一致，如果是整型时 -- 大小,UNSIGNED 必须一致，如果字符 -- 型长度可以不同，但编码必须一致 -- c.外键列如果不是索引，MYSQL引擎 -- 会自动将外键列定义为索引并且自动生成外键名CONSTRAINT employ1_ibfk_1 -- d.外键列(子表)添加信息，参考表必须有相应信息， -- 参考表不能删除/更新在子表中已经使用的外键值。

-- 完整格式 -- FOREIGN KEY (外键列名称) -- REFERENCES 参考表名称(字段名称) -- [ON UPDATE RESTRICT(默认)|CASCADE|SET NULL|NO ACTION -- ON DELTED RESTRICT(默认)|CASCADE|SET NULL|NO ACTION] -- 说明： -- a.RESTRICT:等同NO ACTION -- 参考表(父表)不能更新/删除外键表(子表)中被应用的字段值 -- b.CASCADE(级联)：参考表(父表) -- 更新/删除外键表(子表)也更新或删除 -- 相应的信息 -- c.SET NULL:参考表(父表) -- 更新/删除将外键表(子表)中 -- 更新或删除相应信息设置为NULL -- 外键表(子表)的字段必须允许为NULL -- 即不能为NOT NULL

-- 先有参考表 create table depart2( id int(2) zerofill primary key auto_increment, dname varchar(30) not null );

insert depart2(id,dname) values (1,'部门1'), (2,'部门2'), (3,'部门3'); -- 再有子表 create table employ2( id int unsigned primary key auto_increment, name varchar(30) not null, depart_id int(2) zerofill not null, foreign key(depart_id) references depart2(id) on update cascade on delete cascade ); --RESTRICT(默认)|CASCADE insert employ2(name,depart_id) values ('alice',1), ('zhangs',1), ('lis',2); --('tom',4); 子表添加的外键值必须在参考表中有对应的值

-- 参考表(父表)更新/删除外键表(子表)也更新或删除相应的信息 -- 更新参考表 id 是 02 的改 6 update depart2 set id=6 where id=02;

-- 删除参考表 id 是 01 delete from depart2 where id=01;

18.子查询

--1.where 或 having 后面：

（1）标量子查询（一个结果）

eg1.谁的工资比alice高？

步骤1.先找alice的工资

select salary from employee where name='alice';

然后将alice的查询结果放到要查询的条件

select * from employee where salary>(select salary from employee where name='alice');

eg2：返回（job_id与1号员工相同），（salary比2号员工多的）员工姓名，job_id 和工资

--(a) 查询与1号员工相同的job_id select job_id from employee where id=1; --(b)查询 2号员工的薪水 select salary from employee where id=2; --c 查询员工姓名，job_id 和工资，条件是job_id等于(a) 结果和salary大于（b）结果 select name,job_id,salary from employee where job_id=( select job_id from employee where id=1 ) and salary >( select salary from employee where id=2 );

eg3：(返回公司工资最少的)员工的name,job_id和salary --(a) 查询公司工资最少工资信息 select min(salary) from employee; --(b) 查询员工的name,job_id和salary 条件salary 等于 (a) 结果 select name,job_id,salary from employee where salary =( select min(salary) from employee ); eg4：查询最低工资大于(100号部门最低工资)的部门id和其最低工资 --(a) 查询100号部门最低工资的部门 select min(salary) from employee where department_id=100;

--(b) 查询每个部门id和其最低工资,条件最低工资大于（a）的结果 select department_id,min(salary) as m from employee group by department_id having m>( select min(salary) from employee where department_id=100 );

（2）多行一列的结果（in,all所有，some/any任意）

-- 运算符 --> >= ALL max(大于最大值) --> >= SOME/ANY min(大于最小值)

--< <= ALL min(小于最小值) --< <= SOME/ANY max(小于最大值) --= SOME/ANY IN

--> >= ALL max(大于最大值) --> >= SOME/ANY min(大于最小值)

--< <= ALL min(小于最小值) --< <= SOME/ANY max(小于最大值) --= SOME/ANY IN

举例：

select * from employee where salary in(4000,5000); -- =some/any 等同 in select * from employee where salary =any(select salary from employee where salary=4000 or salary=5000); -- >= ALL max(大于最大值) select * from employee where salary >all(select salary from employee where salary=4000 or salary=5000);

-- >= any min(大于最小值) select * from employee where salary >any(select salary from employee where salary=4000 or salary=5000);

-- <= ALL min(小于最小值) select * from employee where salary <all(select salary from employee where salary=4000 or salary=5000);

-- <= any max(小于最大值) select * from employee where salary <any(select salary from employee where salary=4000 or salary=5000);

#案例1：返回其它工种中比job_id为02工种任一工资低的员工的员工号、姓名、job_id 以及salary
-- a.查找job_id为02工种的工资
select salary from employee where job_id=2
-- b.查询员工的员工号，姓名，job_id以及salary ,条件 salary 要低于a的任意结果
select id,name,job_id,salary from employee where salary<any(select salary from employee where job_id=2) and job_id<>2;
-- 等同于
select id,name,job_id,salary from employee where salary<(select max(salary) from employee where job_id=2) and job_id<>2;
#案例2：返回其它部门中比job_id为02部门所有工资都低的员工   的员工号、姓名、job_id 以及salary（练习）
select id,name,job_id,salary from employee where salary<all(select salary from employee where job_id=2) and job_id<>2;

（3）一行多列字段

#案例：查询员工编号最小并且最高工资的员工
select *
from employee
where (id,salary)=(select min(id),max(salary) from employee);
-- ------------------等同于--------------------------
select *
from employee
where id=(
select min(id) from employee
) and salary=(
select max(salary) from employee
);

--2.select后面(新字段)

#案列：查询每个部门的员工个数及部门所有信息
select d.*,(select count(*) 
from employee as e 
where e.department_id=d.department_id as 个数
from department as d;

+------+---------------+-----------------+------+
| id   | department_id | department_name | 个数 |
+------+---------------+-----------------+------+
|    1 |           100 | 部门1           |    2 |
|    2 |           101 | 部门2           |    2 |
|    3 |           102 | 部门3           |    4 |
|    4 |           103 | 部门4           |    2 |
+------+---------------+-----------------+------+
4 rows in set (0.00 sec)

----------------------------------法2---------------------------------
select d.department_id,d.department_name,count(*) as 个数
from employee as e
inner join department as d 
on e.department_id=d.department_id
group by d.department_id;

+---------------+-----------------+------+
| department_id | department_name | 个数 |
+---------------+-----------------+------+
|           100 | 部门1           |    2 |
|           101 | 部门2           |    2 |
|           102 | 部门3           |    4 |
|           103 | 部门4           |    2 |
+---------------+-----------------+------+
4 rows in set (0.00 sec)

--3.from后面（产生新表）！！！

#产生新表
#将子查询结果充当一张表，要求必须起别名
#案例：查询（平均工资最低的）部门信息和该部门的平均工资
select d.*,av.avg
from department as d
inner join(
		select avg(salary) as avg,department_id
		from employee
		group by department_id
	  	order by avg asc
		limit 0,1
)as av 
on d.department_id=av.department_id;

+----+---------------+-----------------+-------------+
| id | department_id | department_name | avg         |
+----+---------------+-----------------+-------------+
|  1 |           100 | 部门1           | 2850.000000 |
+----+---------------+-----------------+-------------+
1 row in set (0.00 sec)


#查询“001”课程比“002”课程成绩高的所有学生的学号
SELECT a.sid,a.score 
FROM (
    SELECT sid,score 
    FROM sC 
    WHERE cid="001"
)as a
INNER JOIN 
(SELECT sid,score 
 FROM sC 
 WHERE cid="002"
) as b
ON a.sid=b.sid
WHERE a.score>b.score;

+-----+-------+
| sid | score |
+-----+-------+
| 002 |    95 |
| 003 |    91 |
| 006 |    90 |
| 007 |    50 |
| 008 |    66 |
+-----+-------+



#查询没有学全所有课的同学的学号、姓名（以前的作业参考）

SELECT sid, sname 
FROM student 
WHERE sid 
NOT IN(
SELECT s.sid 
    FROM student s
	INNER JOIN sC as sc 
    ON s.sid=sc.sid
	GROUP BY s.sid
	HAVING COUNT(sc.cid)
    =(SELECT COUNT(cid) FROM course)
);

+-----+-------+
| sid | sname |
+-----+-------+
| 009 | 李丽  |
| 010 | 李梅  |
+-----+-------+

--4.exisis

#exists(完整的查询语句)

#案例1：查询有员工的部门名

#in方法
SELECT department_name
FROM department d
WHERE d.department_id IN(
	SELECT department_id
	FROM employee
);
+-----------------+
| department_name |
+-----------------+
| 部门1           |
| 部门2           |
| 部门3           |
| 部门4           |
+-----------------+

#exists方法
SELECT department_name
FROM department d
WHERE EXISTS(
	SELECT * FROM employee e
	WHERE d.department_id=e.department_id
);

+-----------------+
| department_name |
+-----------------+
| 部门1           |
| 部门2           |
| 部门3           |
| 部门4           |
+-----------------+

#查询学过“100”并且也学过编号“102”课程的同学的学号、姓名
SELECT s.sid, s.sname
FROM student s
	INNER JOIN sC as sc 
	ON s.sid=sc.sid
	WHERE  sc.cid=001 AND 
	EXISTS(
     SELECT * FROM sC AS sc2 
     WHERE sc2.sid=sc.sid 
     AND sc2.cid=002
);

+-----+-------+
| sid | sname |
+-----+-------+
| 001 | 赵雷  |
| 002 | 张峰  |
| 003 | 李云  |
| 004 | 吴兰  |
| 005 | 郑竹  |
| 006 | 王菊  |
| 007 | 周梅  |
| 008 | 孙峰  |
+-----+-------+

SELECT s.sid, s.sname
FROM student s
INNER JOIN sC as sc 
ON s.sid=sc.sid
WHERE  sc.cid=001 and sc.sid in (
    select sc2.sid 
    from sC 
    as sc2 where sc2.sid=sc.sid 
    and sc2.cid=002
);

+-----+-------+
| sid | sname |
+-----+-------+
| 001 | 赵雷  |
| 002 | 张峰  |
| 003 | 李云  |
| 004 | 吴兰  |
| 005 | 郑竹  |
| 006 | 王菊  |
| 007 | 周梅  |
| 008 | 孙峰  |
+-----+-------+

--4DCL 操作：grant ,revoke

grant --权限 revoke--撤销

--不会得话到cmd执行代码 ? grant

1.权限操作

--（1）创建用户
     CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
--（2）赋予权限
     GRANT ALL ON company.* TO 'jerry'@'localhost';
     -- GRANT ALL 给予所有权限 	company.* company的所有表
--（3）查看用户权限
     SHOW GRANTS FOR jerry@localhost;
--（4）在 root 用户下查看所有 有哪些用户
	 use mysql;
     SELECT User, Host FROM mysql.user;
--（5）在 root 用户下查看所有 有哪些用户
	drop user jerry@localhost;

2.更新用户密码

	 use mysql;
     ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
     FLUSH PRIVILEGES;  -- 刷新

3.撤回权限

revoke 权限数据库名.表名 from用户名@登录方式:

	revock all on company.* from jerry@localhost;
	-- 撤回company下所有表的操作权限

4.数据库的导入导出：

-- 注意：一定退出mysql命令提示符
-- 此操作不在用户账号内操作，直接在cmd操作界面
	-- 导出
	mysqldump -u root -p company2 > d:/companyback.sql
	-- 导入
	mysql -uroot -p mytest < d:/companyback.sql

19.索引

1.索引概述:(提高查询效率的）

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索（查询）速度。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

2.索引分类

普通索引，主键索引，外键索引，全文索引fulltext

3.查看索引

desc employee;

show create table 表名;

show indexes from 表名;

mysql> show indexes from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
/*
1.Table 表的名称。 
2.Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。
3.Key_name 索引的名称。 
4.Seq_in_index 索引中的列序列号，从1开始。 
5.Column_name 列名称。 
6.Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。 
7.Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。 
8.Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 
9.Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。 
10.Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。 11.Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。 12.Comment 多种评注。
*/

4.普通索引

(1) 创建索引

-- 格式：CREATE INDEX indexName ON employee(name[(length)]);
create index index_sex on employee(sex);
-- 如果是char,varchar类型，length可以小于字段实际长度;
-- 如果是blob和text类型，必须指定length

(2) 修改表结构(添加索引)

-- 格式：ALTER table employee ADD INDEX indexName2(name);
alter table employee add index index_age(age);

(3) 创建表的时候直接指定

/* 格式：
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(16))  
); 
*/
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(16))  
);

(4) 删除索引的语法

-- 格式：ROP INDEX  [indexName]  ON mytable; 

alter table employee drop index indexName2;

5.唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一：

(1)创建索引

-- 格式： CREATE UNIQUE INDEX indexName ON employee(name(length)); 
create unique index unique_username on myta1(username);

(2) 修改表结构

-- 格式： ALTER table employee ADD UNIQUE [indexName] (name(length));
alter table myta1 add unique unique_id(id);

(3)创建表的时候直接指定

/*格式
CREATE TABLE mytable3(  
 ID INT NOT NULL primary key,   
 username VARCHAR(16) NOT NULL,  
 UNIQUE unidue_id (username)
); 
*/
CREATE TABLE myta2(  
 ID INT NOT NULL primary key,   
 username VARCHAR(16) NOT NULL,  
 UNIQUE(username)  
);

(4)删除唯一索引

-- 两种方法
drop index unique_id on mytables3;

alter table mytables3 drop index unique_id;

6.使用ALTER 命令添加和删除索引

!** 有四种方式来添加数据表的索引：**!

(1)ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
-- 例如： 
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

(2)ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

(3)ALTER TABLE tbl_name ADD INDEX index_name (column_list):

添加普通索引，索引值可出现多次。

(4)ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):

该语句指定了索引为 FULLTEXT ，用于全文索引。 注意：如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

(5) ALTER删除索引

ALTER TABLE testalter_tbl DROP INDEX c;
ALTER TABLE testalter_tbl DROP PRIMARY KEY;
-- 注意： 如果主键是自增的列,应该先取消自增,才能删除,ALTER TABLE testalter_tbl MODIFY id int;

7.主键索引

--显示主键信息--

SHOW INDEX FROM table_name\G;

(1)添加主键索引

alter table myta1 add primary key(id);

(2)删除主键

注意!!!：如果主键是自增的列,应该先取消自增,才能删除,ALTER TABLE employee MODIFY id int;

alter table myta1 drop primary key;

(3).外键索引

注意!!!：

1.参考表 id 必须主键

2.参考表和子表的外键类型必须一致

-- 添加外键 [constraint employee_fk1]
alter table employee add  foreign key(job_id) references job(id);

(4)删除外键：

-- 注意: 先删除外键约束关系,然后删除Mysql添加的对应索引 

-- (1) 解除外键约束关系
alter table employee drop foreign key employee_fk1;
-- (2) 删除索引
alter table employee drop index employee_fk1;

重要重要上面所有查询 like范围值不起作用！！！

(5)全文索引FULLTEXT

(将来有第三方技术实现中文全文索引)

--ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list):
/*
该语句指定了索引为 FULLTEXT ，用于全文索引。
--注意：如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
*/

8..组合索引

CREATE TABLE mytable4(  
ID INT NOT NULL,   
col_a VARCHAR(16) NOT NULL, 
col_b VARCHAR(16) NOT NULL, 
col_c VARCHAR(16) NOT NULL,  
INDEX abc_index (col_a,col_b,col_c)  
);

总结：

组合索引从最左开始组合，只要包含第一列的查询都会用到该组合索引

为什么组合索引遵循最左优先原则？

索引方法是Btree，树状的，搜索时需要从根节点出发，上层节点对应靠左的值，所以有最左优先原则。

-- 未使用索引的情况 （没有使用col_a所以会出现未使用索引情况）以下↓↓

where col_b = "some value"

where  col_c = "some value"

where  col_b = "some value" and col_c = "some value 

where  col_c = "some value" and col_b = "some value"0

20.视图

1.什么是视图

数据库视图被称为“虚拟表”，允许您查询其中的数据。

视图 create view 只是保存了sql逻辑增删改查，只是一般不能增删改

表 create table 保存了数据增删改查

2.格式

-- 一. CREATE VIEW <视图名> AS <SELECT语句>

-- 【案例 1】查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT name,department_name,job_title
FROM employee e
JOIN department d ON e.department_id  = d.department_id
JOIN job j ON j.job_id  = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

3.视图的修改

# 方式一：
/*
create or replace view 视图名
as
查询语句
eg:
select * from employee
*/
create or replace view myview3
as select name from employee

#方式二
/*
语法：
alter view 视图名
as 
查询语句；
*/
alter view myview3
as select salary from employee;

#删除视图
/*
语法：
drop view 视图名，视图名，...；
*/
drop view myview3;

#查看视图
DESC myv1;
show create view myv1;

#五.视图的更新（了解）
#具备以下特点的视图不允许更新数据
#包含以下关键字的sql语句：
-- 分组函数
-- distinct
-- group by
-- having、union
-- union all

21.事务

#TCL
Transaction Control Language -- 事务控制语言

1.事务概念：

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

2.事务的特性：

ACID 原子性：一个事务不可再分割，要么都执行要么都不执行 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态 隔离性：一个事务的执行不受其他事务的干扰 持久性：一个事务一旦提交，则会永久的改变数据库的数据.1

3.事务的创建

# 隐式事务：事务没有明显的开启和结束的标记
-- 比如insert、update、delete语句

delete from 表 where id =1;

# 显式事务：事务具有明显的开启和结束的标记
-- 前提：必须先设置自动提交功能为禁用

set autocommit=0;

show variables like 'autocommit'

-- 步骤1：开启事务
set autocommit=0;
start transaction;可选的
-- 步骤2：编写事务中的sql语句(select insert update delete)
-- 语句1;
-- 语句2;
-- ...

-- 步骤3：结束事务
-- 逻辑：得到sql的result结果集 if(result){commit；提交成功}else{
rollback;-- 回滚失败
}
commit; -- 提交事务
rollback; -- 回滚事务

4.savepoint 节点名

1.设置保存点

# 事务的隔离级别：
		                            脏读           不可重复读	               幻读
read uncommitted（读取未提交内容）：    √		          √		                  √
read committed（读取提交内容）：        ×		       √		               √      ORCALE默认
repeatable read（可重读）：            ×		         ×		                 √      MySQL默认
serializable （可串行化）              ×               ×                       ×

# 这三者都是数据库事务的错误情况。

-- 1、脏读：事务A读到了事务B未提交的数据。也就是说,当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

-- 2、不可重复读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到row1，但内容发生了变化。

-- 3、幻读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到两行记录row1和row2。

mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
-- 查看隔离级别
select @@transaction_isolation ;--tx_isolation;
-- 设置隔离级别
set session|global transaction isolation level 隔离级别;


-- 开启事务的语句;

-- 结束事务的语句;

SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;

#1.演示事务的使用步骤

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE employee SET salary = 1000 WHERE name='tom';
UPDATE employee SET salary = 1000 WHERE name='alice';

#结束事务
ROLLBACK;
#commit;

SELECT * FROM account;
#2.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM employee WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM employee WHERE id=28;
ROLLBACK TO a;#回滚到保存

SELECT * FROM account;

22.变量

1.分类

系统变量： 全局变量会话变量

自定义变量： 用户变量局部变量

2.系统变量

#一、系统变量
-- 说明：变量由系统定义，不是用户定义，属于服务器层面
-- 注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

#1》全局变量
#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

#2》会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量
SHOW SESSION VARIABLES;

3.自定义变量

1.用户变量

2.局部变量

#  --------------1》用户变量----------------
/*
作用域：针对于当前会话（连接）有效，作用域同于会话变量
*/

# ---------------2》局部变量----------------
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#案例：声明两个变量，求和并打印


#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

#用户变量和局部变量的对比

		    作用域			 定义位置		      语法
用户变量	当前会话		会话的任何地方		加@符号，不用指定类型
局部变量	定义它的BEGIN END中 	BEGIN END的第一句话	一般不用加@,需要指定类型

4.存储过程

32326/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率


#注意：
/*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/

#调用语法:
CALL 存储过程名(实参列表);

#一、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END
delimiter $
#1.创建带in模式参数的存储过程
-- 注意：过程语句用分号隔开，与mysql的命令提示符下的分号冲突
-- 将mysql的命令提示符下的分号改为‘$’ delimiter $
#案例1：创建存储过程实现 根据女生名，查询对应的男神信息
delimiter $
CREATE PROCEDURE myp1(IN username VARCHAR(20))
BEGIN
	SELECT e.name,e.salary,d.department_name
	FROM employee as e
	INNER JOIN department as d 
	ON e.department_id = d.department_id
	WHERE e.name=username;
END $

#调用
CALL myp1('tom')$

#2 ：创建存储过程实现，用户是否登录成功  procedure

CREATE PROCEDURE myp2(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用
END $

#调用
CALL myp2('张三','8888')$

#3.创建out 模式参数的存储过程
#案例1：根据输入的员工信息，返回对应的部门名称

CREATE PROCEDURE myp3(IN userName VARCHAR(20),OUT departmentName VARCHAR(20))
BEGIN
	SELECT e.name,e.salary,INTO departmentName
	FROM employee as e
	INNER JOIN department as d ON e.department_id = d.department_id
	WHERE e.name=userName;	
END $

delimiter;
CALL myp3('张三',@dname)$
SELECT @dname;


#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
delimiter$
CREATE PROCEDURE myp4(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp4(@m,@n)$
SELECT @m,@n$


#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

#四、查看存储过程的信息
DESC myp2;×
SHOW CREATE PROCEDURE  myp2;

5.函数

#函数
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

*/

#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
/*

/*

注意：
1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

*/

#二、调用语法
SELECT 函数名(参数列表)


#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN2
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employee;
	RETURN c;
	
END $

SELECT myf1()$


#2.有参有返回
#案例1：根据员工名，返回它的工资

CREATE FUNCTION myf2(dName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employee
	WHERE name = dName;	
	RETURN @sal;
END $

SELECT myf2('tom') $


#案例2：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employee e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('部门1')$

#三、查看函数

SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;

23.流程控制语句

1.顺序、分支、循环

-- -----------------流程控制结构-----------------------

# 顺序、分支、循环


#一、分支结构
#1.if函数
/*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面

*/

#2.case结构
/*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end 

情况2：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 

应用在begin end 中或外面


*/

#3.if结构

/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if

只能应用在begin end 中

*/

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;	
END $

SELECT test_if(87)$

#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500


CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
	
END $

CALL test_if_pro(2100)$

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$

2.循环结构

#二、循环结构
/*
分类：
while、loop、repeat

循环控制：

iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于  break，跳出，结束当前所在的循环

*/

#1.while
/*

语法：

【标签:】while 循环条件 do
	循环体;
end while【 标签】;

联想：

while(循环条件){

	循环体;
}

*/

#2.loop
/*

语法：
【标签:】loop
	循环体;
end loop 【标签】;

可以用来模拟简单的死循环



*/

#3.repeat
/*
语法：
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;


*/

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
	
END $

CALL pro_while1(100)$


/*

int i=1;
while(i<=insertcount){

	//插入
	
	i++;

}

*/


#2.添加leave语句

#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $


CALL test_while1(100)$


#3.添加iterate语句

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $


CALL test_while1(100)$

/*

int i=0;
while(i<=insertCount){
	i++;
	if(i%2==0){
		continue;
	}
	插入
	
}

---演示操作步骤---

当输入代码错误无法跳出时可以CTR+C 跳出

1.多表联合查询

/*综合练习，3月9日作业*/
-- 表结构
-- student(sid,sname,sage,ssex) 学生表
create table student(
	sid int(3) unsigned zerofill primary key auto_increment comment '学生编号',
	sname varchar(30) not null,
	sage tinyint unsigned not null,
	ssex enum('男','女') not null   -- 枚举类型
) comment '学生表'; 

insert student values
	(1,'赵雷',18,'男'),
	(2,'张峰',18,'男'),
	(3,'李云',19,'男'),
	(4,'吴兰',20,'女'),
	(5,'郑竹',18,'男'),
	(6,'王菊',20,'男'),
	(7,'周梅',18,'女'),
	(8,'孙峰',19,'男'),
	(9,'李丽',19,'女'),
	(10,'李梅',18,'女');

--course(cid,cname,tid) 课程表 
create table course(
	cid int(3) unsigned zerofill primary key auto_increment comment '课程编号',
	cname varchar(30) not null comment '课程名称',
	tid int(2) unsigned zerofill not null comment '教师编号'
) comment '课程表'; 

insert course values
	(1,'语文',1),
	(2,'数学',2),
	(3,'英语',3);

--sC(sid,cid,score) 成绩表 
--  初次了解:  多对多 : 一个学生编号可以学多个课程编号；一个课程又可以被多个学生编号学习。
create table sC(
	id int unsigned primary key auto_increment,
	sid int(3) unsigned zerofill not null comment '学生编号',
	cid int(3) unsigned zerofill not null comment '课程编号',
	score int not null comment '成绩' 
) comment '成绩表'; 

insert sC (sid,cid,score) values
	(1,1,87),
	(1,2,90),
	(1,3,91),
	(2,1,95),
	(2,2,93),
	(2,3,92),
	(3,1,91),
	(3,2,88),
	(3,3,92),
	(4,1,89),
	(4,2,90),
	(4,3,94),
	(5,1,90),
	(5,2,91),
	(5,3,91),
	(6,1,90),
	(6,2,85),
	(6,3,91),
	(7,1,50),
	(7,2,38),
	(7,3,82),
	(8,1,66),
	(8,2,50),
	(8,3,51);



--teacher(tid,tname) 教师表
create table teacher(
	tid int(2) unsigned zerofill primary key auto_increment,
	tname varchar(30) not null
) comment '教师表'; 

insert teacher values
	(1,'张三'),
	(2,'李四'),
	(3,'王五');


--1.查询“001”课程的所有学生的学号与分数；

SELECT sid,score FROM sC WHERE cid="001" order by score desc;
--1-2查询“001”课程的所有学生的名称与分数；
SELECT s.sname,sc.score 
FROM sC as sc 
INNER JOIN student as s
ON sc.sid = s.sid
WHERE sc.cid="001" 
order by sc.score desc;

--2.查询平均成绩大于等于60分的同学的学号和平均成绩；
-- 选择成绩表
SELECT sid,AVG(score) as a
FROM sC
GROUP BY sid
HAVING a>=60;
--3.查询所有同学的学号、姓名(student)、选课数、总成绩(sC)

-- 初次了解：  一对多设计: 一个成绩只能属于一个学生编号，一个学生编号对应多个成绩

-- 对 sid 学生编号基于 成绩表(sC 里有重复的sid 值)分组

SELECT s.sid AS "学号", s.sname AS "姓名", COUNT(sc.cid) AS "课程数目", SUM(sc.score) AS "总分数"
FROM student as s
INNER JOIN sC as sc
ON s.sid=sc.sid
GROUP BY s.sid
order by 总分数 desc;



--4.查询姓“李”的老师的个数；
select count(*)
from teacher
where tname like '李%';

--5.查询学过“张三”老师课的同学的学号、姓名
SELECT s.sid AS "学号", s.sname AS "姓名"
FROM student s
INNER JOIN sC as sc ON s.sid=sc.sid
INNER JOIN course as c ON c.cid=sc.cid
INNER JOIN teacher as t ON  t.tid=c.tid
WHERE t.tname="张三";

--6.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

--  成绩表中对  cid 分组
SELECT cid AS "课程ID", MAX(score) AS "最高分", MIN(score) AS "最低分"
FROM sC as sc
GROUP BY cid



--7.查询没有学全所有课的同学的学号、姓名

SELECT sid, sname FROM student 
WHERE sid NOT IN(
SELECT s.sid FROM student s
INNER JOIN sC as sc ON s.sid=sc.sid
GROUP BY s.sid
HAVING COUNT(sc.cid)=(
SELECT COUNT(cid) FROM course))

--8.查询学过“100”并且也学过编号“101”课程的同学的学号、姓名
SELECT s.sid, s.sname
FROM student s
INNER JOIN sC as sc ON s.sid=sc.sid
WHERE  sc.cid="001" AND EXISTS(
     SELECT * FROM sC AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="002"
);

--9.查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT a.sid,a.score FROM (SELECT sid,score FROM sC WHERE cid="001")as a
INNER JOIN (SELECT sid,score FROM sC WHERE cid="002") as b
ON a.sid=b.sid
WHERE a.score>b.score;

mysql> use java1;
Database changed
mysql> show tables;
Empty set (0.02 sec)

mysql> create table user(
    -> id int,
    -> name varchar(30),
    -> age int
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc user; //查看表字段
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from user;
Empty set (0.01 sec)

mysql> insert user(id,name,age) value
    -> (1,'tom',20),
    -> (2,'alice',19),
    -> (3,'rose',18);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user\G;
*************************** 1. row ***************************
  id: 1
name: tom
 age: 20
*************************** 2. row ***************************
  id: 2
name: alice
 age: 19
*************************** 3. row ***************************
  id: 3
name: rose
 age: 18
3 rows in set (0.00 sec)

mysql> select * from user;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   20 |
|    2 | alice |   19 |
|    3 | rose  |   18 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------
mysql> create table emp(
    -> ename varchar(10) primary key,
    -> hiredate date,
    -> sal decimal(10,2),
    ->  deptno int(2)
    -> );
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | NO   | PRI | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
----------------------------------------------------------------------------3月3日－－－－－－－－－－－－－
mysql> use mytest;

mysql> desc admin；
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| adminName | varchar(15) | YES  |     | NULL    |       |
| password  | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

insert admin (id,adminName,password) values
    -> (1,'tom',17),
    -> (2,'alice',18),
    -> (3,'jerry',20);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from admin;
+------+-----------+----------+
| id   | adminName | password |
+------+-----------+----------+
|    1 | Simon     | 1223     |
|    2 | Tom       | 3366     |
|    3 | Kitte     | 8689     |
|    1 | tom       | 17       |
|    2 | alice     | 18       |
|    3 | jerry     | 20       |
+------+-----------+----------+
6 rows in set (0.00 sec)


Database changed
mysql> create table cms_admin(
    -> id int unsigned primary key auto_increment,
    -> name varchar(30) not null unique,
    -> password char(32) not null,
    -> age int unsigned not null default 18,
    -> email varchar(50) not null unique,
    -> salary decimal(10,2) not null default 1000
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> desc cms_admin;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int unsigned  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)   | NO   | UNI | NULL    |                |
| password | char(32)      | NO   |     | NULL    |                |
| age      | int unsigned  | NO   |     | 18      |                |
| email    | varchar(50)   | NO   | UNI | NULL    |                |
| salary   | decimal(10,2) | NO   |     | 1000.00 |                |
+----------+---------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> insert cms_admin (name,password,email) values　　／／给表添加数据
    -> ('tom','1234','11@163.com'),
    -> ('alice','1234','12@123.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from cms_admin;
+----+-------+----------+-----+------------+---------+
| id | name  | password | age | email      | salary  |
+----+-------+----------+-----+------------+---------+
|  1 | tom   | 1234     |  18 | 11@163.com | 1000.00 |
|  2 | alice | 1234     |  18 | 12@123.com | 1000.00 |
+----+-------+----------+-----+------------+---------+
2 rows in set (0.00 sec)
-----------------------------------------------------------------------
／／1.修改表类型，将ａｇｅ默认值default改为20
mysql> alter table cms_admin modify　　　
    -> age tinyint unsigned not null default 20;
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc cms_admin;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)      | NO   | UNI | NULL    |                |
| password | char(32)         | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 20      |                |
| email    | varchar(50)      | NO   | UNI | NULL    |                |
| salary   | decimal(10,2)    | NO   |     | 1000.00 |                |
+----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
-----------------------------------------------------------------------
／／2.添加字段
mysql>  alter table cms_admin add addr varchar(30) not null after age;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
／／和上面的初始表对照
mysql> desc cms_admin;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)      | NO   | UNI | NULL    |                |
| password | char(32)         | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 20      |                |
| addr     | varchar(30)      | NO   |     | NULL    |                |
| email    | varchar(50)      | NO   | UNI | NULL    |                |
| salary   | decimal(10,2)    | NO   |     | 1000.00 |                |
+----------+------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
----------------------------------------------------------------------
／／3.删除表中某一行字段
mysql> alter table cms_admin drop addr,drop email;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
／／和上面的初始表对照
mysql> desc cms_admin;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)      | NO   | UNI | NULL    |                |
| password | char(32)         | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 20      |                |
| salary   | decimal(10,2)    | NO   |     | 1000.00 |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
-----------------------------------------------------------------------
／／4.更改字段名
mysql> alter table cms_admin change name userName varchar(30) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cms_admin;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| userName | varchar(30)      | NO   | UNI | NULL    |                |
| password | char(32)         | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 20      |                |
| salary   | decimal(10,2)    | NO   |     | 1000.00 |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
-----------------------------------------------------------------------
／／5.修改字段排列顺序
mysql> alter table cms_admin modify salary decimal(10,2) not null default 1000 after userName;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cms_admin;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| userName | varchar(30)      | NO   | UNI | NULL    |                |
| salary   | decimal(10,2)    | NO   |     | 1000.00 |                |
| password | char(32)         | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 20      |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

-----------------------------------------------------------------------
／／6.更改表名　　　　将ｃｍｓ＿ａｄｍｉｎ改成ｃｍｓ＿ａｄ
mysql> alter table cms_admin rename to cms_ad;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| admin            |
| cms_ad           |
+------------------+
2 rows in set (0.01 sec)
-----------------------------------------------------------------------
／／添加字段操作
mysql> insert cms_ad values(9,'jhon',888.88,'2323',22)
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from cms_ad;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | tom      | 1000.00 | 1234     |  18 |
|  2 | alice    | 1000.00 | 1234     |  18 |
|  9 | jhon     |  888.88 | 2323     |  22 |
+----+----------+---------+----------+-----+
3 rows in set (0.00 sec)
----------------------------------------------------------------------
／／更新数据
mysql> update cms_ad set age =27 where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from cms_ad;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | tom      | 1000.00 | 1234     |  18 |
|  2 | alice    | 1000.00 | 1234     |  18 |
|  9 | jhon     |  888.88 | 2323     |  22 |
+----+----------+---------+----------+-----+
3 rows in set (0.00 sec)
----------------------------------------------------------------------
／／数据更新增加1
mysql> update cms_ad set age=age+1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from cms_ad;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | tom      | 1000.00 | 1234     |  19 |
|  2 | alice    | 1000.00 | 1234     |  19 |
|  9 | jhon     |  888.88 | 2323     |  23 |
+----+----------+---------+----------+-----+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------
／／删除表中行的数据
mysql> delete from cms_ad where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from cms_ad;
+----+----------+---------+----------+-----+
| id | userName | salary  | password | age |
+----+----------+---------+----------+-----+
|  1 | tom      | 1000.00 | 1234     |  19 |
|  9 | jhon     |  888.88 | 2323     |  23 |
+----+----------+---------+----------+-----+
2 rows in set (0.00 sec)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
／／删除表　表不存在
mysql> delete from cms_ad;
Query OK, 2 rows affected (0.01 sec)

mysql> select *from cms_ad;
Empty set (0.00 sec)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
／／在重新创建一个表并将数据添加上，然后使用truncate语句来清除表。注意是清除
mysql> create table cms_admin(
    -> id int unsigned primary key auto_increment,
    -> name varchar(30) not null unique,
    -> password char(32) not null,
    -> email varchar(50) not null unique
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert cms_admin (name,password,email) values
    ->  ('tom','1234','11@163.com'),
    ->  ('alice','1234','12@123.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from cms_admin;
+----+-------+----------+------------+
| id | name  | password | email      |
+----+-------+----------+------------+
|  1 | tom   | 1234     | 11@163.com |
|  2 | alice | 1234     | 12@123.com |
+----+-------+----------+------------+
2 rows in set (0.00 sec)

mysql> truncate cms_admin;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from cms_admin;
Empty set (0.01 sec)

-----------------------------------------------------------------------
／／查询某个属性　不要用*来查询
mysql> select id,email from cms_admin;
+----+------------+
| id | email      |
+----+------------+
|  1 | 11@163.com |
|  2 | 12@123.com |
+----+------------+
2 rows in set (0.00 sec)
／／给表起别名，然后查询
mysql> select c.id,c.email from cms_admin c;
+----+------------+
| id | email      |
+----+------------+
|  1 | 11@163.com |
|  2 | 12@123.com |
+----+------------+
2 rows in set (0.00 sec)
-----------------------------------------------------------------------3月5日

enla5

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础

MySQL是一种基于ANSI的标准计算机语言一：数据查询语言（DQL:Data Query Language）：其语句，也称为“bai数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY 和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。二：数据操作语言（DML：Data Manipulation Language）：其语句包括动词IN
复制链接

扫一扫