京津冀地区某机构内参,四月不能错过的MySQL数据定义语言

简单DCL

-- 创建用户
create user 'xx'@'%' identified by 'password';
-- 赋权
grant all privileges on 数据库.表名 to 'xx'@'%' identified by 'password';
-- 刷新权限
flush privileges;
-- 创建数据库
create database day_02;
-- 使用day_02数据库
use day_02;
-- 创建t_user表
create table t_user(
    `username` varchar(20),
    `password` varchar(20)
)default charset = utf8;
-- 加字段注释
create table xx(
   id int comment '编号';
)
-- 插入数据
-- insert into 表名 (列,列...) values (值,值...)
insert into t_user(username,password) values ('admin','root');
insert into t_user(username) values('test1');
insert into t_user(username,password) values('test','2'),('test','3');
(格式必须严格一致)错误写法:insert into t_user values('test');
-- 删除数据
delete from t_user;
-- 删除时为了避免误删 最好加上where语句进行限定
delete from t_user where username = 'test';
-- 更新数据
update t_user set password = '123';
-- 更新时不加where限定会全部更改
update t_user set password = '123' where username = 'test1';
-- 先执行表限定,然后执行行限定,最后执行列限定
-- select 列限定 from 表限定 where 行限定
-- 表限定:查询那张表
-- 行限定:查询那些符合条件的数据,不加where是全表查询
-- 列限定:要查看的结果值,如果是所有列,就写*,多个列用逗号隔开
-- 查询所有用户信息
select * from t_user;
-- 查询表中用户名为admin的用户密码
select password from t_user where username = 'damin';
-- 查询表中用户名为admin的用户名和用户密码
select password,username from t_user where username = 'admin';
-- 撤权 授权和撤权的时候可以不叫identified by 'password'
revoke all privileges on 数据库.表名 from 'xx'@'%' identified by 'password';
-- 删除表
drop t_user;
-- 删除数据库
drop day_o2;
-- 删除用户
drop user 'xx'@'%';
-- 避免数据库已经有同名表已经存在不执行后续SQL语句
create table if not exists user_info(id int);
-- 避免删除不存在表报错
drop table if exists xx;
简单的DDL
-- 更改表名
alter table 表名 rename 新表名;
-- 更改字段名 (列名)
alter table 表名 change 列名 新表名 数据类型;
-- 添加字段 默认尾部添加
alter table user_info add nickname varchar(20);
-- 首部添加
alter table user_info add sex char(1) first;
-- 指定已有列后面
alter table user_info add id int after name;
-- 删除列
alter table user_ifo drop sex;
-- 更改字段类型
alter table user_info modify id varchar(10);
-- 更改字段注释
alter table user_info modify nickname varchar(10) comment '昵称';

增强版DDL

上面的DDL只是一系列的基本操作,让我们有库有表可以插入数据,但是对于插入的数据是否有效,并不能保证,比如我们能够插入毫无用处的数据,这种记录会浪费存储空间,为了避免类似情况,MySQL提供了一系列完整性验证机制;
约束类型
主键 外键 唯一 非空 自增 默认值 关键字
primary key
foreign key
unique
not null
auto_increment
default
在Java中我们使用equals()方法和hashCode方法来判断对象是否相等,那么怎么在数据库表述数据的唯一性?
主键通常用于唯一确定表中一条记录,设置主键的字段不能为NULL,而且不能重复;
主键可以设置在一个字段,也可以在多个字段,基本都是一个字段;
主键的设置可以分为两种:
创建表语句的时候,添加主键约束:

create table person(
    id int primary key,
    name varchar(20)
);
create table person(
    id int,
    name varchar(100),
    income decimal(18,2),
    primary key(id,name)
);
create table person(
    id int,
    name varchar(20),
    primary key(id,name)
);
-- 错误写法
create table person(
    id int primary key,
    name varchar(20) primary key
);

创建表完成后,通过alter添加主键约束:

create table person(
    id int,
    name varchar(20)
);
alter table person add primary key(id,name);

如果主键的值用户输入,很可能会导致输入一致不成功,于是自增出现,自增的字段必须是有主键约束的;

create table test_05(
    id int auto_increment,
    name varchar(20),
    primary key(id)
);

-- 添加的时候自动生成id 会自动按照最大值增加
insert into test_05(name) values('a');
-- 设置主键自增是从1000开始
alter table test_05 auto_increment = 1000;
-- 在创建完表后再加自增
create table test_06(
    id int,
    name varchar(10),
    primary key(id)
);

alter table test_06 modify id int auto_increment;
insert into test_06(name) values('w');

除了主键,还有外键,它是关联完整性中的一种约束,它可以限定两张有外键关系的表中,一张表中的字段数据需要参考另外一张表中主键值,下面这个studet和teacher两张表中,syudent的teacher_id就必须是teacher表中id已经有的值,不能写其他值;
这里提出一个问题,如果在第三张表上存放两张表的外键,那么可以出现其中一个外键不同,另外一个外键相同的情况吗?就好像重载方法;

-- foreign 外国的 references 以...为参考
create table teacher(
    id int,
    name varchar(20),
    primary key(id)
);
create table student(
    id int,
    name varchar(20),
    teacher_id int,
    primary key(id),
    foreign key(teacher_id) references teacher(id)
);
-- 第一题答案是可以,有两个外键的情况下,组合不同即可
CREATE TABLE st(
   sid INT,
   tid INT,
   FOREIGN KEY(sid) REFERENCES s(id),
   FOREIGN KEY(tid) REFERENCES t(id)
);
-- 在建表后设置外键
alter table student add foreign key(teacher_id) references teacher(id);
-- 删除外键
ALTER table s DROP FOREIGN KEY t_id;

唯一约束是指table的列或者列组合不能重复,保证数据唯一性,唯一约束不允许有null出现;

-- unique唯一的
create table test_01(
    id int,
    name varchar(20),
    unique(username)
);
-- 创建表后设置唯一性
alter table test_01 add unique(username);
-- 顺便说一下设置为不能为空
-- default是设置默认值
create table test_03(
    id int not null,
    name varchar(20) default '无名氏',
    sex varchar(2) not null default '男';
);
-- 老规矩,建表后设置
alter table test_03 modify name varchar(20) not null default '无名氏';

这里提出第二个问题,什么时候用modify,什么时候用change,什么时候用add,什么时候用drop,什么时候用delete,什么时候用revoke,什么时候用grant,什么时候用creat,什么时候用insert,什么时候用alter?

向表单中添加列用add,修改表单的列属性列名不变用modify,修改表单的列名和属性用change,删除表单的列用drop,删除表中数据用delete,删除用户权限用revoke,赋给用户权限用grant,插入元素用inset,字段约束和字段属性修改用alter;

-- 在MySQL中,且(and)的优先级是大于或(or)的;
select * from student where name='张三' and score>90;
select * from student where name='张三' or score>90;
-- 先允许and后允许or
select * from student where id=2 or name='张三' and score>98;

MySQL中没有==,只用=,其实也是数据库里面赋值语句是insert又不是=,然后不等于则是<>,并不是!=;
比起其他语言,MySQL的SQL语句更像是英语命令,就好像对小爱同学说,给我女朋友打电话,它会先检索你有没有女朋友,然后告诉你,你没有女朋友;

SELECT `name`,phone FROM student WHERE girl IS NULL;
-- 相当于 score>=98 and score<= 100
select * from student where score between 98 and 99.2;
-- 批量插入
select * from student where id in(1,3,5,9,11);
-- 批量删除
delete * from student where id in(3,5,6);

下面说一下模糊查询like,其中%匹配任意个数的任意字符,_匹配单个任意字符;

SELECT `name`,score FROM student WHERE `name` LIKE '__';
-- 排序
select 列限定 from 表限定 order by 列名 asc/desc;
-- 去重
select distinct score from student  order by score desc;
-- 限制前三条数
select * from student order by score desc limit 3;
-- 从第三个数值开始取两个数值
select * from student order by score desc limit 3,2;
-- 最小值
select min(score) from student;
-- 最大值
select max(score) from stundet;
-- 最大值
select sum(score) from student;
-- 平均值 值为NULL不参与计算
select avg(score) from stundet;
-- count用来统计表中或者数组中记录的一个函数
count(*):总条数;
max(字段名):最大值;
min(字段名):最小值;
avg(字段名):平均值;
sum(字段名):总和;
-- group by根据给定的数据列的每个成员对查询结果进行分组统计,最后得到一个分组汇总表
-- having可以让我们筛选分组后的数据,where子句在聚合前进行筛选记录,也就是作用在group by和having子句前
-- 先执行group by,再执行having,最后执行 order by
select count(*) from student where score>90;
select teacher_id,count(*) as stu_count from student group by teacher_id;
select * from student where id=2 group by teacher_id;
select avg(score) as avgScore,teacher_id from student group by teacher_id having avgScore>=75;
-- Case 类似于Java中的switch,它在不改变表的情况下,对数据进行等级分类
select id,name,score,
    case
        when score<60 then '差'
       when score>=60 and score<80 then '良'
       when score>=80 then '优'
    end as '等级'
from test_01;

子查询又叫嵌套查询,它通常位于select后,from后.where后;
当子查询位于select后面的时候,一定要找好两表对应的关系,子查询中只能有一个字段,最好使用别名,这样能够让查询更加强袭,别名可以用来命令新的字段,也可以用来命名新表;

select max(score),min(score),sum(score),avg(score),count(*),(
    select name from teacher where id=teacher_id
)as teacher_name from student group by teacher_id;

当子查询位于from后面,我们可以将子查询当作一张表,因为子查询会优先被执行,子查询的别名,可以让别的查询当作表或者列去操作;

select *,
    case rank
    when 'A' then '优'
    when 'B' then '良'
    when 'C' then '差'
    end rank_ch
from(
    select *,
    case
    when score<60 then 'C'
    when score>=60 and score<80 then 'B'
    when score>=80 then 'A'
    end as rank
from student
)a;

当子查询在where后面的时候,多条数据间要用in不能用=,如果确定子查询的结果为一行一列,就可以用=,如果返回结果为多行一列的话,要用in,一列是必须的,必须是一列,子查询中的select后面只能有一个字段;

select * from student where teacher_id in(
    select id from teacher where name='张老师'
);

union和union all都是用于合并查询,前者会自动去除重复项,后者不会,去重的依据是union的时候,select出来的字段如果对应相等则认为是同一条的记录,但是后者效率更高,我们最好使用union all去操作,去重交给代码完成;
使用两者的时候需要注意,参与合并的表,它们select出来的字段数量必须一致,字段类型减一一一对应,字段顺序减一一致;

select * from student where teacher_id=1 union select * from student where score>60;
select * from student where teacher_id=1 union all select * from student where score>60;

最后列数SQL语句中常用的函数:

select version()显示当前MySQL软件的版本;
select database()显示当前所处的数据库是那个;
select char length('中国')返回字符个数;
selet length('中国')返回字符所占字节数,通常一个UTF8的汉字占3个字节;
select concat('a','b')返回'ab',字符串拼接函数;
select concat_ws('=','a','b')返回'a=b=c',字符串拼接函数,第一个是拼接间隔符;
select upper('abcd')返回ABCD,字符转大写;
select lower('ABCD')返回abcd,字符变小写;
select substring('系统信息类','1','2')返回系统信息,第2个参数代表从1开始的第几个字符,第三个参数代表截取的字符个数;
select trim(' abc')返回abc,删除左右空格;
select curdate()返回当前日期;
select curtime()返回当前时间;
select now()返回当前日期时间;
select unix_timestamp()返回当前日期时间对应的时间戳(秒);
select unix_timestamp('2018-05-24 20:00:00')返回参数指定的日期时间对应的时间戳;
select from_unixtime(15164654)返回参数之i的那个的时间戳;
select datediff('2018-05-23',now())返回两个日期相差的天数;
select adddate(now(),-2)返回指定天数前;
select year('2019-02-24')返回年份
select month()返回月份;
select day()返回日;
select if(<判断条件>,<条件为真的返回值>,<条件为假的返回值>)相当于Java中的三目运算符;
select ifnull(<表达式或者字段>,<表达式或者字段为null时的返回值>)通常用给可能有null的情况下的提供默认值;
select ifnull(null,'无名氏')null这里可以写列名,就会把该列的null以无名氏返回;
select ifnull(name,'无名氏')from teahcer;