5--MySQL基础

行者无敌

1.概述

MySQL是一个关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (关系数据库管理系统) 应用软件之一。
使用MySQL来存储并操作数据，则需要满足以下要求：

• 安装MySQL服务端
  
• 安装MySQL客户端
  
• 【客户端】连接【服务端】
  
• 【客户端】发送命令给【服务端】，服务端接受命令并执行相应操作(增删改查等)
  

客户端连接：
管理员默认为root，没有设置密码则按回车直接登录
　　mysql -u root -p -h host
设置mysql密码
　　mysql admin -u root password 123456
修改密码
　　mysql admin -u root -p password abcdef
客户端退出：
　　quit 或 exit 或 \q
操作内容：

 2.库操作

1、创建数据库：create命令用于创建数据库。

1. 1 mysql> create database <数据库名>;

复制代码

2、显示所有数据库

1. 1 mysql> show databases;

复制代码

3、修改数据库：ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
使用 ALTER 命令及 DROP 子句来删除【user_info】表的 age 字段：

1. 1 mysql> ALTER TABLE user_info  DROP age;

复制代码

注意：如果数据表中只剩余一个字段则无法使用 DROP 来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 【user_info】 中添加 age 字段，并定义数据类型：

1. 1 mysql> ALTER TABLE user_info ADD age INT;

复制代码

如果需要修改字段类型及名称, 可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。
例如，把字段 mobile 的类型从 CHAR(1) 改为 CHAR(11)，可以执行以下命令：

1. 1 mysql> ALTER TABLE user_info MODIFY mobile CHAR(11);

复制代码

在 CHANGE 关键字之后，紧跟着的是要修改的字段名，然后指定新字段名及类型。

1. 1 # 修改字段名称
2. 2 mysql> ALTER TABLE user_info CHANGE mobile phone CHAR(11);
3. 3 # 修改字段名称+数据类型
4. 4 mysql> ALTER TABLE user_info CHANGE mobile phone VARCHAR;

复制代码

4、删除数据库：drop命令用于删除数据库。

1. 1 mysql> drop database <数据库名>;

复制代码

执行以上命令后，i 字段会自动添加到数据表字段的末尾。
5、使用数据库

1. 1 mysql> use <数据库名>;

复制代码

3.表操作

1、创建表
创建MySQL数据表需要以下信息：

• 表名
  
• 表字段名
  
• 定义每个表字段
  

以下为创建MySQL数据表的SQL通用语法：

1. 1 CREATE TABLE table_name(column_name column_type);

复制代码

创建数据表article：

1. 1 CREATE TABLE `article`  (
2. 2   `pk_id` int NOT NULL AUTO_INCREMENT,
3. 3   `title` varchar(255) NOT NULL COMMENT '文章标题',
4. 4   `author_id` int NOT NULL COMMENT '文章作者ID',
5. 5   `category_id` int NULL COMMENT '文章分类',
6. 6   `reading_quantity` int NULL COMMENT '阅读量',
7. 7   `status` int NULL COMMENT '文章状态，0:草稿箱 1:审核中 2:已发布',
8. 8   `release_date` timestamp NULL COMMENT '文章发布时间',
9. 9   `update_date` timestamp NULL COMMENT '文章修改时间',
10. 10   PRIMARY KEY (`pk_id`)
11. 11 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

• 如果不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
  
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  
• PRIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键，列间以逗号分隔。
  
• ENGINE 设置存储引擎，CHARSET 设置编码。
  

2、插入数据

1. 1 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (1, 'MySQL基础总结', 1, 1, 5000, 0, '2021-09-03 16:18:54', '2021-09-03 16:18:56');
2. 2
3. 3 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (2, 'MySQL索引', 1, 1, 4000, 0, '2021-09-03 16:23:32', '2021-09-03 16:23:34');
4. 4
5. 5 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (3, 'MySQL事务', 1, 1, 3000, 0, '2021-09-03 16:24:20', '2021-09-03 16:24:22');

复制代码

3、查询数据

1. 1 select * from article where pk_id in(1,2);

复制代码

查询pk_id等于1和2的数据
4、修改数据

1. 1 update article set title="MySQL基础总结(精品)" where pk_id = 1;

复制代码

把 title 设置为MySQL基础总结(精品)，只针对pk_id=1这行记录
5、删除数据

1. 1 delete from article where pk_id = 1;

复制代码

把pk_id等于1的记录删除
6、清空表数据

1. 1 truncate article;

复制代码

7、修改表注释

1. 1 alter table article comment '文章表';

复制代码

8、表查询
先插入测试数据：

1. 1 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (1, 'MySQL基础总结', 1, 1, 5000, 0, '2021-09-03 16:18:54', '2021-09-03 16:18:56');
2. 2
3. 3 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (2, 'MySQL索引', 1, 1, 4000, 0, '2021-09-03 16:23:32', '2021-09-03 16:23:34');
4. 4
5. 5 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (3, 'MySQL事务', 1, 1, 3000, 0, '2021-09-03 16:24:20', '2021-09-03 16:24:22');

复制代码

（1）like，模糊查询

1. 1 ## 模糊查询：like，%表示匹配任意个字符，_表示匹配一个字符
2. 2 ## 查询article表标题含有MySQL的文章
3. 3 select * from article where title like '%MySQL%';

复制代码

（2）in 操作符允许在 WHERE 子句中规定多个值

1. 1 select * from article where reading_quantity in (5000,6000);

复制代码

（3）or操作符允许在 WHERE 子句中满足其中一个要求

1. 1 select * from article where reading_quantity = 5000 or author_id = 2;

复制代码

查询阅读量等于5000或者作者ID等于2的文章
（3）and操作符允许在 WHERE 子句中同时满足所有要求

1. 1 select * from article  where author_id = 1 and reading_quantity >= 5000;

复制代码

查询作者ID等于1且阅读量大于等于5000的文章
（4）order by，排序

1. 1 select * from article order by reading_quantity asc

复制代码

asc升序、desc降序
（5）limit，分页查询

1. 1 # 从1开始，返回两条记录
2. 2 select * from article limit 1,2;

复制代码

limit(初始记录行的偏移量是 0，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目)
（6）distinct，去重

1. 1 select distinct title from article;

复制代码

按title去重
（7）group by，分组查询

1. 1 select author_id,count(0) from article where status = 1 group by author_id;

复制代码

查询每个作者发布了多少篇文章
（8）case

1. 1 select *, (
2. 2     case
3. 3     when reading_quantity >= 5000 then
4. 4             '热点文章'
5. 5     when reading_quantity >= 4000 then
6. 6             '优质文章'
7. 7     when reading_quantity >= 3000 then
8. 8             '普通文章'
9. 9      else
10. 10             '低质量文章'
11. 11      end
12. 12     ) '文章等级'
13. 13 from
14. 14     article

复制代码

转换为代码形式易于理解

1. 1 if(reading_quantity >= 5000){
2. 2
3. 3 }else if(reading_quantity >= 4000){
4. 4
5. 5 }else if(reading_quantity >= 3000){
6. 6
7. 7 }else{
8. 8
9. 9 }

复制代码

4.数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
1.数值类型
（1）整数类型

• 整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。
  
• 如果只希望表示零和正整数，可以使用无符号关键字“unsigned”对整数类型进行修饰。
  

各个类别存储空间及取值范围如下：

 （2）小数类型

• decimal (length, precision) 用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。
  
• 例如： decimal (5,2)表示小数取值范围：-999.99～999.99 decimal (5,0)表示： -99999～99999的整数。
  

各个类别存储空间及取值范围如下：

 2.日期和时间类型

• 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
  
• 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
  
• TIMESTAMP类型有专有的自动更新特性。
  

各个类别存储空间及取值范围如下：

 3.字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
各个类别存储空间及取值范围如下：

 注意：

• char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
  
• CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
  
• BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
  
• BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
  
• 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同。
  

5.函数

MySQL提供了众多功能强大、方便易用的函数，使用这些函数，可以极大地提高用户对于数据库的管理效率，从而更加灵活地满足不同用户的需求。
准备工作：
创建表

1. 1 -- 文章表
2. 2 CREATE TABLE `article`  (
3. 3   `pk_id` int NOT NULL AUTO_INCREMENT,
4. 4   `title` varchar(255) NOT NULL COMMENT '文章标题',
5. 5   `author_id` int NOT NULL COMMENT '文章作者ID',
6. 6   `category_id` int NULL COMMENT '文章分类',
7. 7   `reading_quantity` int NULL COMMENT '阅读量',
8. 8   `status` int NULL COMMENT '文章状态，0:草稿箱 1:审核中 2:已发布',
9. 9   `release_date` timestamp NULL COMMENT '文章发布时间',
10. 10   `update_date` timestamp NULL COMMENT '文章修改时间',
11. 11   PRIMARY KEY (`pk_id`)
12. 12 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

插入数据

1. 1 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (1, 'MySQL基础总结', 1, 1, 5000, 0, '2021-09-03 16:18:54', '2021-09-03 16:18:56');
2. 2
3. 3 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (2, 'MySQL索引', 1, 1, -4000, 0, '2021-09-03 16:23:32', '2021-09-03 16:23:34');
4. 4
5. 5 INSERT INTO `game`.`article`(`pk_id`, `title`, `author_id`, `category_id`, `reading_quantity`, `status`, `release_date`, `update_date`) VALUES (3, 'MySQL事务', 1, 1, 3000, 0, '2021-09-03 16:24:20', '2021-09-03 16:24:22');

复制代码

为了方便演示绝对值等函数，把pk_id等于2的阅读量设置为-4000
1.MySQL数字函数
（1）ABS(x)
描述：返回 x 的绝对值

1. 1 select pk_id,abs(reading_quantity) from article;

复制代码

pk_id等于1的记录，reading_quantity是-4000，通过abs(reading_quantity)就变成了4000
（2）AVG(expression)
描述：返回一个表达式的平均值，expression 是一个字段

1. 1 select AVG(reading_quantity) from article;

复制代码

（3）MAX(expression)
描述：返回字段 expression 中的最大值

1. 1 select max(reading_quantity) from article;

复制代码

（4）MIN(expression)
描述：返回字段 expression 中的最小值

1. 1 select min(reading_quantity) from article;

复制代码

（5）CEIL(x)
描述：向上取舍

1. 1 SELECT CEIL(1.5)  
2. 2 -> 2
3. 3 SELECT CEIL(-1.5)  
4. 4 -> -1

复制代码

（6）FLOOR(x)
描述：向下取舍

1. 1 SELECT FLOOR(1.5)  
2. 2 -> 1
3. 3 SELECT FLOOR(-1.5)  
4. 4 -> -2

复制代码

（7）ROUND(x)
描述：四舍五入

1. 1 SELECT ROUND(1.4)
2. 2 -> 1
3. 3 SELECT ROUND(1.5)  
4. 4 -> 2

复制代码

（8）SUM(expression)
描述：返回指定字段的总和

1. 1 select sum(reading_quantity) from article;

复制代码

2.MySQL字符串函数
（1）CHARACTER_LENGTH(s)
描述：返回字符串 s 的字符数

1. 1 select pk_id,title,CHARACTER_LENGTH(title) from article;

复制代码

（2）CONCAT(s1,s2…sn)
描述：将字符串 s1,s2 等多个字符串合并为一个字符串

1. 1 select pk_id,CONCAT(title,author_id) from article;

复制代码

把title字段和author_id合并起来。
（3）LEFT(s,n)
描述：返回字符串 s 的前 n 个字符

1. 1 select pk_id,left(title,1) from article;

复制代码

（4）RIGHT(s,n)
描述：返回字符串 s 的后 n 个字符

1. 1 select pk_id,right(title,1) from article;

复制代码

（5）LOWER(s)
描述：将字符串 s 的所有字母转换为小写

1. 1 SELECT LOWER('JAVA')
2. 2  -> java

复制代码

（6）UPPER(s)
描述：将字符串是s的所有字母转换为大写

1. 1 SELECT LOWER('java')  
2. 2 -> JAVA

复制代码

（7）LTRIM(s)
描述：去掉字符串 s 开始处的空格

1. 1 SELECT LTRIM("    JAVA")
2. 2 -> JAVA

复制代码

（8）REPLACE(s,s1,s2)
描述：将字符串 s2 替代字符串 s 中的字符串 s1

1. 1 SELECT REPLACE('abc','a','x')
2. 2 -> xbc

复制代码

（9）REVERSE(s)
描述：将字符串s的顺序反过来

1. 1 SELECT REVERSE('JAVA')
2. 2 -> AVAJ

复制代码

（10）SUBSTR(s, start, length)
描述：从字符串 s 的 start 位置截取长度为 length 的子字符串

1. 1 select pk_id,substr(title,1,2) from article;

复制代码

3.MySQL时间和日期函数
（1）CURDATE()
描述：返回当前日期

1. 1 SELECT CURDATE();
2. 2 -> 2021-08-18

复制代码

（2）NOW()
描述：返回当前日期和时间

1. 1 SELECT NOW()
2. 2 -> 2021-09-03 17:14:15

复制代码

（3）MONTH(d)
描述：返回日期 d 中的月份值，1 到 12

1. 1 select pk_id,month(release_date) from article;

复制代码

（4）DAYNAME(d)
描述：返回日期 d 是星期几，Monday 到 Sunday

1. 1 select pk_id,dayname(release_date) from article;

复制代码

（5）WEEK(d)
描述：计算日期 d 是本年的第几个星期，0 到 53

1. 1 select pk_id,week(release_date) from article;

复制代码

6.连接查询

准备数据：

1. 1 CREATE TABLE `dept` (
2. 2   `department_id` int NOT NULL AUTO_INCREMENT COMMENT '部门编号',
3. 3   `dname` varchar(255) NULL COMMENT '部门名字',
4. 4   `location` varchar(255) NULL COMMENT '地址',
5. 5   PRIMARY KEY (`department_id`)
6. 6 ) COMMENT = '部门表';
7. 7
8. 8 CREATE TABLE `employee` (
9. 9   `employee_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
10. 10   `c_name` varchar(255) NULL COMMENT '员工中文名',
11. 11   `e_name` varchar(255) NULL COMMENT '员工英文名',
12. 12   `hiredate` timestamp(0) NULL COMMENT '雇佣日期，入职日期',
13. 13   `salary` int(11) NULL COMMENT '薪水',
14. 14   `comm` int(11) NULL COMMENT '奖金',
15. 15   `job_id` int(11) NULL COMMENT '所属工种',
16. 16   `department_id` int(11) NULL COMMENT '部门编号',
17. 17   `manager_id` int(11) NULL COMMENT '直接领导编号',
18. 18   PRIMARY KEY (`employee_id`)
19. 19 ) COMMENT = '员工表';
20. 20
21. 21 -- 表中插入数据
22. 22 insert into dept values(10,'财务部','北京');
23. 23 insert into dept values(20,'研发部','上海');
24. 24 insert into dept values(30,'销售部','广州');
25. 25 insert into dept values(40,'行政部','深圳');
26. 26 insert into dept values(50,'人力资源','南京');
27. 27 -- 表中插入数据
28. 28 insert into employee values(1,'小红','xiaohong','1980-12-17',7902,800,1,10,2);
29. 29 insert into employee values(2,'铁蛋','tiedan','1981-02-20',7698,1600,3,30,3);
30. 30 insert into employee values(3,'张三','zhangsan','1981-02-22',7698,1250,5,30,4);
31. 31 insert into employee values(4,'李四','lisi','1981-04-02',7839,2975,2,20,5);
32. 32 insert into employee values(5,'王老五','wanglaowu','1981-09-28',7698,1250,1,40,0);
33. 33 insert into employee values(6,'赵六','zhaoliu','1981-05-01',7839,2850,3,50,5);

复制代码

内连接：

• 等值连接
  
• 非等值连接
  

1. 1 -- 内连接
2. 2 select  e.c_name,d.dname,d.location from employee e inner join dept d on e.department_id= d.department_id;

复制代码

外连接：

• 左外连接
  
• 右外连接
  

1. 1 -- 左外连接，是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null
2. 2 -- 查询employee表中的所有数据和dept表中与employee中相匹配的数据，若是没有匹配的就显示null
3. 3 select e.c_name,d.dname from employee e left outer join dept d  on d.department_id = e.department_id ;

复制代码

1. 1 -- 右外连接和左外连接只不过是左右表相换也能达到同样的效果
2. 2 -- 查询dept部门表对应所有部门和employee表中与之对应的数据，可以发现employee中有6条数据，只显示了5条数据，因为有一个人的部门号60在dept中没有数据，所以就没有显示出来
3. 3 select e.c_name,d.dname from employee e right outer join dept d  on d.department_id = e.department_id;

复制代码

自连接

1. 1 -- 自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名
2. 2 -- 查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表
3. 3 select e.c_name 员工名,b.c_name 上司名 from employee e  left join employee b on e.manager_id= b.employee_id;

复制代码

 

来源:https://www.cnblogs.com/lxl3344/p/17604183.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！