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