MYSQL

MYSQL入门笔记

1.查看已有库

Show databases;

2.创建库

Create databases 库名 [character set utf8]

3.切换库

Use 库名；

4.查看当前所在库

Select database();

5.删除库

Drop database 库名；

6.创建表：

create table 表名（字段名 数据类型 约束，字段名 数据类型 约束……字段名 数据类型 约束）

字段约束：

*如果你想设置数字为无符号则加上unsigned

*如果你不想字段为NULL可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错

*DEFAULT表示设置一个字段的默认值

*auto_increment定义列为自增的属性，一般用于主键，数值会自动+1

*primary key关键字用于定义列为主键。主键的值不能重复，且不能为空。

Create table class_1 (id int primary key auto_increment,name varchar(30) not null,age tinyint unsigned,sex enum(‘m’,’w’,’o’),score float default 0);

7.查看数据表

Show tables;

8.查看表结构

Desc 表名;

9.查看数据表创建信息

show create table 表名

10.删除表

Drop table 表名

11.插入

Insert into 表名 values (值1)，(值2)；

Insert into 表名 values(字段1，字段2……) values (值1),(值2)…

Insert into class_1 values (2,’baron’,10,’m’,91),(3,’jame’,9,’m’,90);

12.查询

Select * from 表名 [where 条件]；

Select 字段1，字段2 from 表名 [where 条件]；

Select * from class_1;
Select name,age from class_1;

13.更新表记录（update）

Update 表名 set 字段1=值1，字段2=值2，…… where 条件；

注：update语句后如果不加where条件，所有记录全部更新

Update class_1 set age=11 where name=’Abby’;
Update class_1 set score=73,sex=’w’ where name=”Eva”;
Update class_1 set score=score+1 where score>90;

14.删除表记录（delete）

Delete from 表名 where 条件；

注：delete语句后如果不加where条件，所有记录全部清空

Delete from class_1 where name=’Abby’;

15.表字段的操作（alter）：

Alter table 表名 执行动作：

添加字段（add）

Alter table 表名 add 字段名 数据类型；

Alter table 表名 add 字段名 数据类型 first；

Alter table 表名 add 字段名 数据类型 after 字段名；

alter table hobby add tel char(11) after price;

删除字段（drop）

Alter table 表名 drop 字段名；

alter table hobby drop level;

修改数据类型（modify）

Alter table 表名 modify 字段名 新数据类型；

alter table modify tel char(16);

修改字段名（change）

Alter table 表名 change 旧字段名 新字段名 新数据类型；

 alter table change tel phone char(16);

表重命名（rename）

Alter table 表名 rename 新表名；

alter table class 1 rename cls;

16.时间数据类型

日期：date

日期时间：datetime，timestamp

时间：time

年份：year

类型	大小（字节）	格式	用途
date	3	YYYY-MM-DD	日期值
time	3	HH:MM:SS	时间值或持续时间
year	1	YYYY	年份值
datetime	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

Select * from marathon where birthday >=”1995-01-01”;
Select * from marathon where performance<”02:30:00”;

17.日期时间函数

Now（）返回服务器当前日期时间，格式对应datetime类型

Curdate（）返回当前日期，格式对应date类型

Curtime（）返回当前时间，格式对应time类型

• 时间操作

时间类型数据可以进行时间和排序等操作，在写时间字符串时尽量按照标准格式书写

Select * from marathon where registration_time<now();
Alter table marathon modify registration_time datetime default now();

18.模糊查询：

Like用于在where子句进行模糊查询，SQL like子句中使用 %来表示任意0个或多个字符，下划线_表示任意一个字符

Select field1，filed2,…fieldN from table_name where field1 like condition1

Select * from class_1 where name like ‘A%’;

Select * from class_1 where name like ‘%E’;

Select * from class_1 where name like ‘___’;

29.As用法：

在sql语句中as用法用于给字段或者表重命名

 Select name as 姓名，age as 年龄 from class_1;
 Select * from class_1 as c where c.age>17;

20.排序：

Order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果

使用order by子句将查询数据排序后再返回数据。

Select field1,field2…fieldN from table_name1 where field1 order by field1[asc [desc]]

Select name as 姓名,age as 年龄from cls;(只在当前语句中生效)

默认情况asc表示升序，desc表示降序

Select * from class_1 where sex=’m’ order by age desc;

复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

Select * from cls order by score desc,age;

21.限制:

Limit 子句用于限制由select语句返回的数据数量或者update，delete语句的操作数量

• 带有limit子句的select语句的基本语法如下：

Select column2,column2,column from table_name where field limit [num];

22.联合查询：

union 操作符用于来连接两个意思的select语句的结果组合到一个结果集中。多个select语句会删除重复的数据

select expression1,expression2,… expression_n from tables [where conditions] union [all | distinct] select expression1,expression2,… expression_n from tables [where conditions];
select * from cls where sex=’m’ union select * from cls where score>90;
select * from cls where sex=’m; union all select * from cls where score>90;
select name,sex,score from cls where sex=’m’ union all select name,age,score from cls where score>90;
select name,sex,score from cls where sex=’m’ union all select name,hobby,price from hobby;

23.子查询:

当一个select语句中包含另一个select查询语句，则称之为有子查询的语句

• 子查询出现的位置：

1.from之后，此时子查询的内容作为一个新的表内容，再进行外层select查询

select name from (select * from class_1 where sex=’m’) as s where s.score>90;

​ 注：需要将子查询结果集重新命名一下，方便where子句中的引用操作

2. where子句中，此时select查询到的内容作为外层查询的条件值

select * from class_1 where age=(select age from class_1 where name=’Tom’;

注：子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列

Where子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录

Select * from (select * from cls where sex=’m’) as man where score >90;
Select cls.name,cls.score,hobby.hobby from cls,hobby where cls.name=hobby.name;
Select * from (select cls.name,cls.score,hobby.hobby from cls,hobby where cls.name=hobby.name) as a where score>90;
Select * from cls where score>(select score from cls where name=”joy”);
Select * from cls where age =(select age from cls where name=”Emma”);
Select * from cls where name in (select name from hobby);

24.聚合分组：

Group by 给查询的结果进行分组

计算每个国家的平均攻击力：
Select country,avh(attack) from sanguo group by country;
对多个字段创建索引，此时多个字段都相同时为一组
Select age,sex,count(*) from class_1 group by age,sex;
对所有国家的男英雄中 英雄最多的前2名的国家及英雄数量
select country,count(id) as number from sanguo where gender='M' group by country order by DESC limit 2;

注：使用分组时select后的字段为group by 分组的字段和聚合函数，不能包含其他内容。group by也可以同时依靠多个字段分组，如group by A,B此时必须A，B两个字段值均相同才算一组。

1.查找所有蜀国人信息,按照攻击力排名
select * from sanguo where country='蜀' order by attack desc;
2.将赵云攻击力设为360 防御设为70
update sanguo set attack=360,defense=70 where name='赵云';
3，将吴国英雄攻击力超过300的改为300,最多改2个
update sanguo set attack=300 where country='吴' and attack>300 limit 2;
4.查找攻击力超过200的魏国英雄名字和攻击力，并且显示为姓名攻击力
select name as 姓名,attack as 攻击力 from sanguo where country=’魏‘ and attack>200;
5，所有英雄按照攻击力降序排序,如果攻击力相同规则按照防御降序排序
select * from sanguo order by attacak desc,defense desc;
6.查找名字为3个字的英雄
select * from sanguo where name like "___";
7,查找比魏国攻击力最高的人攻击力还要高的蜀国英雄，找到魏国防御力排名前2的英雄
select * from sanguo where country='蜀' and attack>(select attack from sanguo where country='魏' order by attack desc limit 1);
8.找到魏国防御力排名前2的英雄
select * from sanguo where country='魏' order by defense desc limit 2;
9，查找所有女性角色同时查找所有男性角色中攻击力少于250的
select * from sanguo where gender='女' union select * from sanguo where gender='男' and attack<250;

25.聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计字段记录的个数

Select avg(attack) from sanguo where country=”蜀”;
Select avg(attack) as 平均攻击 from sanguo;
Select max(attack) as 最大攻击 from sanguo where country=”魏”;
Select max(attack),min(attack) from sanguo where country=”魏”;
Select count(defense) from sanguo;
Select count(*) from sanguo;
Select country from sanguo group by country;
select scountry,avg(attack) from sanguo group by country;
select country,count(*) from sanguo where gender=”男” group by country;
select country,count(*) as num from sanguo where gender=”男” group by country order by num desc limit 1;
select country,gender,count(*) from sanguo group by country,gender;
select country,avg(attack),max(attack) from sanguo group by country;

26.聚合筛选

• having语句

  对分组聚合后的结果进行进一步筛选

  eg1.找出平均攻击力大于105的国家的前2名，显示国家名称和平均攻击力
  select country,avg(attack) from sanguo 
  group by country 
  having avg(attack)>105
  order by avg(attack) desc
  limit 2;
  

  注：

  1.having语句必须与group by联合使用

  2.having语句存在弥补了where关键字不能与聚合函数联合使用的不足，where只能操作表中实际存在的字段

  select country,avg(attack),max(attack) from sanguo group by country having avg(attack)>250;
  select country,avg(attack) from sanguo where gender='男' group by country having avg(attack)>290;
  

27.去重语句

• distinct语句

  不显示字段重复值

  eg1:表中都有哪些国家
  	select distinct name,country from sanguo;
  eg2:计算一共有多少个国家
  	select count(distinct country) from sanguo;
  

  注：distinct和from之间所有字段都相同才会去重

28.聚合运算

• 查询表记录是做数学运算

运算符：+ - * / %

eg1：查询时显示攻击力翻倍
	select name,attack*2 from sanguo;
eg2: 更新蜀国所有英雄攻击力*2
	update sanguo set attack=attack*2 where country='蜀国';

练习：使用book表完成
1.统计每位作家图书的平均价格
select author,avg(price) from book group by author;
2.统计每个出版社出版图书的数量
select publication,count(*) from book group by publication;
3.查看总共有多少个出版社
select count(distinct publication) from book;
4.筛选出哪些出版过超过50元的图书的出版社，并按照其出版图书的平均价格降序排序
select publication,avg(price) from book group by publication having max(price) >= 50 order by avg(price) desc;
5.统计相同时间出版图书的最高价格和最低价格
select publication_date,max(price),min(price) from book group by publication_date;

29.索引操作

• 定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

• 优缺点
  • 优点：加快数据检索速度，提高查找效率
  • 缺点：占用数据库物理存储空间，当对表中数据更新时，索引需要动态维护，降低数据写入效率

注意：

1.通常我们只在经常进行查询操作的字段上创建索引

2.对于数据量很少的表或者进行写操作而不是查询操作的表不适合创建索引

30.索引分类

• 普通（MUL）

普通索引：字段值无约束，key标志位MUL

• 唯一索引（UNI）

唯一索引（unique）：字段值不允许重复，但可为NULL，key标志为UNI

• 主键索引（PRI）

一个表中只能有一个主键字段，主键字段不允许重复，且不能为NULL，key标志为PRI。通常设置记录编号字段id，能唯一锁定一条记录。

31.索引创建

• 创建表时直接创建索引

  create table 表名（
  字段名 数据类型，
  字段名 数据类型，
  index 索引名（字段名），
  index 索引名（字段名），
  unique 索引名（字段名）
  ）;
  

• 在已有的表中创建索引

create [unique] index 索引名 on 表名（字段名）

eg.
create unique index name_index on cls(name);

use books;
create table index_test (id int primary key auto_increment,name varchar(30),index nameindex(name));
create table index_test (id int auto_increment,name varchar(30) primary key(id),index nameindex(name));
create table index_test (id int atuo_increment,name carchar(30),primary key(id),unique nameindex(name));
create table index_test (id int auto_increment,name carchar(30),primary key(id));
create unique index nameindex on cls(name);

• 主键索引添加

alter table 表名 add primary key(id);

• 查看索引

desc 表名；  --> KEY 标志为：MUL、UNI；
show index from 表名；

• 删除索引

drop index 索引名 on 表名；
alter table 表名 drop primary key;        #删除主键

• 扩展：借助性能查看选项去查看索引功能

set profiling=1;         打开功能（项目上线一般不打开）
show profiles       查看语句执行信息

32.外键约束

• 约束：约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性，唯一性
• foreign key功能：建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关联性更强，为了具体说明创建如下部门表和人员表
• 示例

#创建部门表
create table dept (id int primary key auto_increment,dname varchar(50) not null);
#创建人员表
create table person(
 id int primary key auto_increment,
 
)

表为主表，B表为从表

• foreign key外键的定义语法

[constraint symbol] foreign key(外键字段)
references tabl_name(主表主键)
[on delete {restrict | cascade | set null | no action}]
[on update {restrict | cascade | set null | no action}]

• 该语法可以在create table 和alter table 时使用

#创建表时之间建立外键
create table person(
 id int primary key auto_increment,
 name varchar(32) not null,
 age enum('m','w','o') default 'o',
 salary decimal(10,2) default 250.00.
 hire_date date not null,
 dept_id int,
 constraint dept_fk foreign key(dept_id) rederences dept(id)
);

#建立表后增加外键
alter table person add constraint dept_fk foreign
key(dept_id) references dept(id);

注意：

1.并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立

2.从表的外键字段数据类型与指定的主表主键应该相同

• 通过外键名称解除外键约束

alter table person drop foreign key dept_fk;
#查看外键名称
show create table person;

注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

• 级联动作
  • restrict(默认): on delete restrict on update restrict
    • 当主表删除记录时，如果从表中有相关联记录则不允许主表删除
    • 当主表更改主键字段值时，如果从表有相关记录则不允许更改
  • cascade:数据级联更新on delete cascade on update cascade
    • 当主表删除记录或更改被参照字段的值时，从表会级联更新
  • set null:on delete set null on update set null
    • 当主表删除记录时，从表外键字段值变为null
    • 当主表更改主键字段值时，从表外键字段变为null

alter table person add foreign key(dept_id) references dept(id) on delete cascade on update cascade;
delete from dept where id=6;

33.表关联设计

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。常见的数据关系如下：

• 一对一关系

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

举例：学生信息和学籍档案，一个学生对应一个档案，一个档案页只属于一个学生

alter table person drop foreignkey person _ibfk_1;
drop index dept_id on person;
alter table person add foreign key(dept_id) references dept(id) on delete set null on update set null;
update dept set id=6 where id=3;
select * from dept;
select name,dept_id from person;