MYSQL基础快速入门

1. SQL类型

1.1 DDL

DDL:data definition language(操作数据库或表时需加database或table)用来定义数据库对象：数据库，表，列等，例如创建、删除、修改：数据库、表结构等

规范：
1.不要使用关键字命名一个表或列。
2.多条sql语句必须以分号分隔，单条语句可加可不加，建议加。
3.sql语句不区分大小写。
4.sql语句可以写成一行或多行。

数据库
增：(create) create database mysql1 character set utf8 collate utf8_general_ci;
删：(drop) drop database mysql1;
改：(alter) alter database mysql1 character set utf8 collate utf8_general_ci;
查：(show/select)

查所有数据库：show databases;
查某数据库创建过程：show create database mysql1;
查询当前当前数据库：select database();
切换数据库：use mysql2;

表结构
增：(create)CREATE TABLE USER
(userId INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(64) NOT NULL,pass_word VARCHAR(64) NOT NULL,age INT(8) NOT NULL DEFAULT 18)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;

表名后面的内容需要使用“()”括起来，里面的内容是表结构，由列名和列类型组成，列名与列类型之间用空格隔开，每一列之间用逗号隔开，最后一列后面不需要加逗号，语句结束后需要加“；”。
建表时可以指定引擎，外键不能跨引擎

删(drop)：drop table user
查
查所有表：show tables;查看所有的列：show columns from table;
help show:显示允许的show语句
查看某表的定义： show create table user;
查看表结构：desc user;

改(alter)
增加列：(add) alter table user add address varchar(256);
修改列(modify)：alter table user modify address varchar(128) not null;
修改列名：（change）alter table user change address hometown varchar(256);
修改表名：(rename to) alter table user rename to manager;
删除列：(drop) alter table user drop hometown;
修改字符集(set)：alter table user character set gbk collate gbk_chinese_ci;
添加外键：ALTER TABLE student ADD CONSTRAINT student_id FOREIGN KEY(id) REFERENCES score(id)

1.2 DML

DML:manipulation(操作时不用加table,直接用表名即可)用来对数据库中表的记录进行更新，例如：增、删、改表记录

增：（insert into）
1.插入的数据应与字段的数据类型相同
2.数据的大小应该在列的长度范围内
3.在values中列出的数据位置必须与被加入的列的排列位置相对应。
4.除了数值类型外，其它的字段类型的值必须使用引号引起。
5.如果要插入空值，可以不写字段，或者插入 null.
6.:对于自动增长的列在操作时，直接插入null值即可.
7.对于not null 的列，插入时必须有值，否则插入失败

所有列都增（列名可省）：insert into user values(null,zhangsan,zhangsan);
不建议省略列名，使用列名插入，可以保证在表结构发生改变的情况下依然能正常插入
增加部分列(列名不可省)：insert into user (username) values(lisi);
批量增加（用逗号隔开）：insert into user (username) values(lisi),(wangwu);
查询插入：insert select : insert into studentNew (student_name,age) select name,age from student;列名不一定要对应，但位置一定要对应

删：（delete from）
1.删除表 drop table 表名
2.删除表中记录
(1).delete from 表名
(2).truncate table 表名
3.关于delete 与truncate的区别?(笔试题)
(1).delete是一行一行删除 truncate是将表结构销毁，在重新创建表结构.如果数据比较多，truncate的性能高。
(2).delete是dml语句 truncate dcl语句
delete是受事务控制. 可以回滚数据.
truncate是不受事务控制. 不能回滚.

删除所有内容：delete from user;
删除指定内容(加限制条件)：delete from user where id = 1;
删除外键：
1.查看外键：show create table student
2.删除外键：ALTER TABLE student DROP FOREIGN KEY student_id

改：（update）
修改所有：(几乎不用) update user set username=‘zhaoliu’,password=‘zhaoliu’;
修改指定内容：update user set username=‘zhaoliu’,password=‘zhaoliu’ where id = 1;
更改多个列的值只需要一个set就够了，然后多个列之间用逗号隔开就行
增加外键：ALTER TABLE score ADD CONSTRAINT student_id FOREIGN KEY(id) REFERENCES student(id)
原则：谁引用了外部数据，外键就建在哪个表，score引用了student的id,则外键就建在score表

1.3 DCL

DCL:control:用来定义数据库的访问权限和安全级别，及创建用户

1.4 DQL

DQL:query:用来查询数据库中表的记录

基本查询
查询指定列：select username from user
查询所有列：select * from user
别名查询(as:可省) select username 用户名 from user;
去重(distinct)：select distinct username from user;
distinct关键字用于所有列，而不仅仅是前置列。如select distinct username,address 除非这俩字段都不相同，否则都会被查询出来。
distinct只能用于列名，可以配合聚合函数使用。如SELECT AVG( DISTINCT age) FROM student
列计算：select ,salary2 as 年终奖 from user
ifnull函数：IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

where子句

1.比较运算符:> >= < <= = !=(<>):select username from user where age>=35;
2.逻辑运算符:and or not : select username from user where age >=35 and gender=‘男’
1.and比or拥有更高的优先级，如果联合使用，建议使用（）分隔。
2.mysql支持not 对in、exsits、between取反
3.between …and：相当于 >= and <=
注意:between 后面的值必须是小值 and后面的是大值：select username from user where age between 15 and 18;
4.模糊查询：通配符使用（1.匹配一个：‘_’；2.匹配多个：‘%’）:select username from user where username like ‘_李%’
以“able”结尾的单词，匹配可能因为末尾空格匹配失败，可以使用函数RTrim()来去掉末尾空格。where RTrim(username) like “%able”
5.in(可以比较多个值):select username from user where age in(18,22,25);
6.null值操作:(is null：判断为空； is not null：判断不为空) select username from user where gender is null;
null不同于0，“”，空格
7.正则表达式。正则表达式规则是相同的，可以百度。当模糊查询起来不方便时可以使用REGEXP。使用regexp binary可以区分大小写
SELECT * FROM student WHERE NAME LIKE ‘%W%’
区分大小写：
SELECT * FROM student WHERE NAME REGEXP BINARY ‘W’

order by排序(默认升序)
1.如果需要按多个列排序，只有第一列数据相同时，才会考虑后面的列。如order by desc salary,age —优先按salary降序排序，且desc只作用于其直接相连的字段，对其后字段如age不起作用。若想在多列上降序，则每列前面都需要desc.
2.在mysql中，A和a是相同的，即通过order by无法排序，若要区分大小写，则需要通过其他手段。
升序：asc：select * from user order by age asc;
降序：desc: select * from user order by height

聚合函数
聚合操作的是某一列数据，会自动忽略值为null的行。
count(*)：不会忽略值为null的行
count(column):会忽略值为null的行
对于跨列求平均值的，可以使用+号
如：（chinese+english+math）/3表示语数英的平均成绩
求和：sum():select sum(salary) from user;
求平均：avg():select avg(age) from user;
统计数量：count():select count(username) from user;
最大值：max():select max(age) from user;
最小值：min():selelct min(salary) from user;

分组操作:group by
having与where的区别:
1.having是在分组后对数据进行过滤. where是在分组前对数据进行过滤
2…having后面可以使用聚合函数,where后面不可以使用聚合函数。
3.where过滤行，having过滤分组。大多数的where都可使用having替代
group by的规定：
1.如果被分组的值为null,则null也会显示为一行，并且汇总所有未null的为一行
select * from user order by address having age >=18;
限制结果：limit ： select name from table limit 2,3;从第三条数据开始，累加三条数据（3，4，5的记录，下标从0开始）。如limit 2,0==limit 2 就只返回前1，2数据。如数据不足，则全部返回。

常用函数

文本处理函数
left():返回左边几个字符
right():返回右边几个字符
trim(),rtrim(),ltrim():去左右、右、左的空格
length():返回串的长度
lower()、upper():转小写，大写
substring();截取子串

日期时间函数
curdate:当前日期curtime:当前时间
year()、month()、dayofweek()、day():返回一个日期的年、月、星期、天
hour()、minute()、second()：
dateformat():格式化
date()、time():返回时间戳的日期、时间部分
其他函数用到再说
where year(order_date)=2020 and month(order_date) = 6 :2020年6月的订单

数值函数：正余弦等数学函数
abs():绝对值
sqrt():平方根
mod(m,n):m除以n的余数
rand():随机数

2. 列的约束

约束英文：constraint
约束实际上就是表中数据的限制条件

主键约束(primary key) PK 1表一个
表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值
给某个字段添加主键约束之后，该字段不能重复也不能为空，效果和”not null unique”约束相同，但是本质不同。
主键约束除了可以做到”not null unique”之外，还会默认添加”索引——index”

一张表应该有主键字段，如果没有，表示该表无效
主键值：是当前行数据的唯一标识、是当前行数据的身份证号
即使表中两行记录相关数据相同，但由于主键值不同，所以也认为是两行不同的记录

无论是单一主键还是复合主键，一张表主键约束只能有一个(约束只能有一个，但可以作用到好几个字段)
单一主键:给一个字段添加主键约束

列级定义：定义在某字段后
mysql> create table t_user(
-> id int(10) primary key,
-> name varchar(32)
-> );
表级定义：定义在语句的末尾，自成一句
mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> constraint t_user_id_pk primary key(id)
-> );

复合主键:给多个字段联合添加一个主键约束(只能用表级定义)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128) unique,
-> primary key(id,name)
-> );

auto_increment
在MySQL数据库提供了一个自增的数字，专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增

非空约束(not null)
用not null约束的字段不能为null值，必须给定具体的数据

创建表，给字段添加非空约束（创建用户表，用户名不能为空）
如果没有插入name字段数据，则会报错
mysql> insert into t_user (id) values(1);
ERROR 1364 (HY000): Field ‘name’ doesn’t have a default value

唯一性约束(unique)
unique约束的字段，具有唯一性，不可重复，但可以为null

列级约束
mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128) unique
-> );

表级约束
单一字段

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128），
-> unique(email)
-> );
如果插入相同email会报错

多个字段（联合约束）联合约束，表示两个或以上的字段同时与另一条记录完全相等，则报错。当与其中的某一个字段相同时，不会报错
表级约束可以给约束起名字
方便以后通过这个名字来删除这个约束
mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128),
-> constraint t_user_email_unique unique(email)
-> );

外键约束(foreign key) FK 只能是表级定义
注意点
外键值可以为null
外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束
有了外键引用之后，表分为父表和子表 。子表为外键所在表
班级表：父表
学生表：子表
创建先创建父表
插入先插入父表数据
删除先删除子表数据

若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。

foreign key(classno) references t_class(cno)

某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值

单一外键：给一个字段添加外键约束
复合外键：给多个字段联合添加一个外键约束 一张表可以有多个外键字段（与主键不同）

检查约束(目前MySQL不支持、Oracle支持)

3. 数据类型

数值类型

TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4字节 单精度
DOUBLE 8字节 双精度
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

字符类型

char与varchar区别?
char是一个定长字符串.指定长度不会随着内容的不足而改变
varchar是一个可变长度的字符串，它根据信息自动改变长度.(只在字符串长度小于指定长度情况下)

CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

日期时间类型

DATE 3字节 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3字节 HH:MM:SS 时间值或持续时间
YEAR 1字节 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4字节
1970-01-01 00:00:00/2038
YYYYMMDD HHMMSS 混合日期和时间值，时间戳

4. 多表查询

笛卡尔积
select * from user,order;

内连接
显式内连接(inner join on )select * from user u inner join order o on u.uid = o.oid;
隐式内连接(where)select * from user u ,order o where u.uid = o.oid;
区别：隐式内连接是在查询结果上做过滤，显式内连接是带着过滤条件去查询，效率更高

外连接应用：用于查询一个表中有，另一个表没有的记录
左外连接：左外连接是将左表中数据全部查出来，如果右表中没有与之对应的数据，则用null代替。右外连接反之亦然。
SELECT * FROM USER u LEFT JOIN orders o ON u.id=o.user_id;
user为主表，显示所有内容

右外连接
SELECT * FROM USER u RIGHT JOIN orders o ON u.id=o.user_id;
右边的表为主表，即orders显示所有内容

分页查询
select * from user limit 3,3;第一个参数是索引，默认从0开始，第二个参数是个数。即从第4个开始，查3个，即4，5，6

子查询（查询的嵌套）
子查询很多情况下可以被关联查询替代，关联查询效率可能更高

查询出高于10号部门平均工资的员工信息SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10);

UNION：组合，并集
UNION几乎总是完成与多个where条件相同的工作。UNION ALL 不会去重，它可以满足查询重复条件的语句

5. 其他知识

触发器
概念：mysql为了响应insert、update、delete语句而执行的一条mysql语句。触发器仅仅支持表，不支持视图和临时表
创建规则：触发器名唯一，关联的表，触发的动作，触发的时机。每张表最多支持六个触发器，每条insert、update、delete的之前和之后
触发器的删除：drop trigger usertrigger
触发器不能更新或覆盖，如果要修改，先删除，后重建

insert、update、delete触发器
在insert触发器代码内，可引用一个名为new 的虚拟_表，访问被插入的行；在before insert触发器中，new 中的值也可以被更新（允许更改被插入的值；对于auto_increment列，在insert之前为0，insert之后包含新的自动更新的值）

create trigger ordertrigger after insert on orders for each row select new.order_num

事务管理
innodb支持事务，myisam不支持事务
insert、update、delete支持事务，create,drop不支持事务
隐含事务关闭：当commit或rollback后，事务自动关闭。mysql自动提交所有更改，当然这个可以改变，在必要的情况下

字符集与校对
查看支持的字符集：show character set
有的字符集不止一种校对

查看支持的所有校对：show collation
ci表示不区分大小写，cs表示区分大小写

如果仅指定character set,则使用此字符集及其默认的校对，若两者均不指定，则使用数据库默认。字符集不仅可以对表设置，还可对字段设置。

在select语句中，如果排序的字段的校对与默认的不同，也可以重新设置

安全管理

在日常的mysql操作中不建议使用root账号，应该创建一系列的账号，设置不同的权限
创建用户：进入mysql数据库的user表
CREATE USER qinshuoyu IDENTIFIED BY ‘520’
identified by ‘520’：指定密码，保存到mysql时会加密
修改名字：rename user qinshuoyu to qin
删除用户：drop user qin
查看权限：show grants for qinshuoyu
设置权限：grant select、insert on study.* to qinshuoyu
整个服务器：grant all
删除权限：revoke select on study.* from qinshuoyu