SQL

数据库概念

在计算机中, 通过一定的结构,来组织,存储和管理数据的软件系统

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能

数据库分类

　　关系型数据库

　　非关系型数据库

约束

　　主键约束 primary key

• 主关键字(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
• 语法

　　　　创建主键

• • • 列级别

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

• • • 表级别(联合主键)

create table temp(id int ,name varchar(20),pwd varchar(20),primary key(id, name));

删除主键

alter table temp drop primary key;

添加主键

alter table temp add primary key(id,name);

　　外键约束 foreign key 

• 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系
• 语法

创建外键

基本外键

-- 主表

create table temp(

id int primary key,

name varchar(20)

);

-- 副表

create table temp2(

id int,

name varchar(20),

classes_id int,

foreign key(id) references temp(id)

);

联合外键

多列外键组合，必须用表级别约束语法

-- 主表

create table classes(

id int,

name varchar(20),

number int,

primary key(name,number)

);

-- 副表

create table student(

id int auto_increment primary key,

name varchar(20),

classes_name varchar(20),

classes_number int,

/*表级别联合外键*/

foreign key(classes_name, classes_number) references classes(name, number)

);

删除外键

alter table student drop foreign key student_id;

增加外键

alter table student add foreign key(classes_name, classes_number) references classes(name, number);

唯一约束 unique

1. 唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
2. 唯一约束不允许出现重复的值，但是可以为多个null。
3. 同一个表可以有多个唯一约束，多个列组合的约束。
4. 在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
5. 唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
6. 语法

　　　　建表唯一

创建表时设置，表示用户名、密码不能重复

create table temp(

id int not null ,

name varchar(20),

password varchar(10),

unique(name,password)

);

添加唯一

alter table temp add unique (name, password);

删除唯一

alter table temp drop index name;

非空约束 not null

1. 非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
2. Null类型特征：所有的类型的值都可以是null，包括int、float 等数据类型
3. 语法

创建非空

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc

create table temp(

id int not null,

name varchar(255) not null default 'abc',

sex char null

)；

通过设置列来设置非空和默认

-- 增加非空约束

alter table temp

modify sex varchar(2) not null;

-- 取消非空约束

alter table temp modify sex varchar(2) null;

-- 取消非空约束，增加默认值

alter table temp modify sex varchar(2) default 'abc' null;

默认值 default

　　　　　　　　mysql 数据库四种模式

1. 1. 1. ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
      2. TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
      3. STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。
      4. STRICT_ALL_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。

　　　　　　　　记录

　　　　　　数据库设计

　　　　Oracle，Microsoft SQL Server，MySQL，PostgreSQL，DB2，Microsoft Access， SQLite，Teradata等

 SQL

DDL(数据库定义语言)

DML(数据操纵语言)

DQL(数据查询语言)

DCL(数据管理语言)

DDL(数据库定义语言)

数据库

创建

CREATE DATABASE [IF NOT EXISTS] <数据库名> [CHARACTER SET utf8]

 

删除

DROP DATABASE <数据库名>

 

查看

　　SHOW DATABASES;

　　　　查看服务中心所有的数据库

　　SHOW CREATE DATABASE <数据库名>;

　　　　查看数据库创建细节

选择

　　USE <数据库名>

数据表

创建

1 CREATE TABLE `t` ( 
2 `id` int(11) NOT NULL auto_increment,
3 `n_id` int(10) unsigned NOT NULL, 
4 `L1` int(10) unsigned zerofill default NULL,
5 `L2` int(11) default NULL, 
6 PRIMARY KEY (`id`,`n_id`), 
7 KEY `f_t_b` (`L1`), 
8 CONSTRAINT `f_t_b` FOREIGN KEY (`L1`) REFERENCES `my` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改表

删除

DROP TABLE <表名>

 

查看

查看表结构  

查看创建语句

SHOW CREATE TABLE <表名>

DML(数据操纵语言)

添加记录

INSERT INTO table_name ( field1, field2,...fieldN )

VALUES

( value1, value2,...valueN );

 

INSERT INTO TABLE_NAME(F1,F2,F3) SELECT (E1,E2,E3) FROM TABLE WHERE.....

 

修改记录

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

删除记录

DELETE FROM table_name [WHERE Clause]

DQL(数据查询语言)

简单查询

完整语法

字段筛选

别名AS

重复数据合并(去除重复的查询结果) distinct

where

比较运算

1. 1. 不等于 <> !=
   2. null值等于 <=>
   3. 大于,小于,等于,大于等于,小于等于

逻辑运算

and

or

and 和or同时使用,and优先级高,可以使用小括号控制顺序

空值判断

is null

is not null

存在判断(判断子查询中是否有结果)

exists

not exists

SELECT * FROM 表名 WHERE
EXISTS (SELECT id FROM my WHERE id = 1111)

in

like

通配符%_

any和all

ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。

ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。

ANY运算符

 

order by

asc

desc

limit

分页应用

 　　union联合查询

　　union -- 默认去重

 

　　union all -- 不去重

　　group by

 　　聚合函数

COUNT MAX MIN SUM AVG

除COUNT函数外，其它聚合函数在执行计算时会忽略NULL值

COUNT

　　SELECT COUNT(*) FROM TABLE_NAME-- 查询表中数据的行数,无论是否有空值

　　SELECT COUNT(COL_NAME) FROM TABLE_NAME-- 查询COL_NAME列中的个数,会忽略掉空值

MAX

　　MAX()函数是用来返回指定列中的最大值

MIN

　　MIN()函数是用来返回指定列中的最小值

SUM

• SUM()是一个求总和的函数，返回指定列值的总和
• 如果在没有返回匹配行SELECT语句中使用SUM函数，则SUM函数返回NULL，而不是0；
• SUM函数忽略计算中的NULL值
• DISTINCT运算符允许计算集合中的不同值；
• SELECT SUM(DISTINCT <列名>) FROM <表名>

AVG

　　AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值

having

• 与 GROUP BY 配合使用，为聚合操作指定条件
• WHERE 子句只能指定行的条件，而不能指定组的条件
• WHERE 先过滤出行，然后 GROUP BY 对行进行分组，HAVING 再对组进行过滤，筛选出我们需要的组
• 其使用的要素是有一定限制的，能够使用的要素有 3 种： 常数 、 聚合函数 和 聚合键 ，聚合键也就是 GROUP BY 子句中指定的列名

子查询

　　在增删改查的SQL中, 包含了另一个查询语句

多表连查

　　交叉连接 CROSS JOIN

　　　　select * from 表1, 表2 where 连接条件

　　　　交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。需要注意的是，交叉连接产生的结果是笛卡尔积，并没有实际应用的意义。

　　　　

内连接 INNER JOIN

　　　　select * from 表1 inner join 表2 on 连接条件

外连接

左外连接

select * from 表1 left [outer] join 表2 on 连接条件

右外连接

select * from 表1 right join 表2 on 连接条件

行列转换

使用case when

 　　　　　　

 if (`字段名1`=‘字段值’，，)

 

DCL(数据控制语言)

用户

授权

数据库表授权

GRANT SELECT,INSERT ON *.* TO 'easy'@'%' WITH GRANT OPTION

GRANT UPDATE (L1, l2) ON st_goods.T TO 'easy'@'%' WITH GRANT OPTION

WITH 关键字后面带有一个或多个参数。这个参数有 5 个选项:

 

 查看用户授权

　　SHOW GRANTS FOR 'username'@'hostname';

常用库权限

 常用表权限

权限表

代码:

  1 -- 创建数据库
  2 CREATE DATABASE IF NOT EXISTS hhr_data CHARACTER SET utf8;
  3 
  4 -- 新建表
  5 CREATE TABLE `user` (
  6     id INT(11) NOT NULL auto_increment PRIMARY KEY,
  7     name VARCHAR(20) NOT NULL,
  8     sex VARCHAR(5) not null DEFAULT '男',
  9     age INT
 10 )DEFAULT CHARSET=utf8
 11 
 12 -- 删除数据库
 13 DROP DATABASE hhr_data;
 14 
 15 -- 删除表
 16 DROP TABLE user;
 17 
 18 -- 查看创建SQL的语句
 19 SHOW CREATE DATABASE hhr_data;
 20 
 21 -- 查看创建表的语句
 22 SHOW CREATE TABLE user;
 23 
 24 -- 查看表信息
 25 DESC user;
 26 
 27 -- 修改表
 28 ALTER TABLE user RENAME TO t_user;
 29 
 30 -- 修改编码格式
 31 ALTER TABLE t_user CHARACTER SET utf8;
 32 
 33 -- 添加列
 34 ALTER TABLE t_user ADD COLUMN CODE VARCHAR(30) DEFAULT '0';
 35 
 36 -- 修改已经存在的列
 37 -- MODIFY是重新定义,对之前所有的定义都要重新加上
 38 -- 比如这句后面没加DEFAULT 那就没有默认值
 39 ALTER TABLE t_user MODIFY CODE VARCHAR(20);
 40 
 41 -- 删除列
 42 ALTER TABLE t_user DROP COLUMN CODE;
 43 
 44 -- 修改列名
 45 ALTER TABLE t_user CHANGE CODE user_code VARCHAR(20) DEFAULT '110';
 46 
 47 -- 在xxx后面添加一列
 48 ALTER TABLE t_user ADD COLUMN weight INT DEFAULT 70 AFTER age;
 49 
 50 -- 修改列位置
 51 ALTER TABLE t_user MODIFY user_code VARCHAR(20) DEFAULT '110' AFTER id;
 52 
 53 -- 添加记录
 54 INSERT INTO t_user values(1 , '200' , '张三' , '男' , 22 , 45);
 55 
 56 INSERT INTO t_user(id , user_code , name , sex , age , weight) values(2 , '200' , '李四' , '男' , 22 , 45);
 57 
 58 INSERT INTO t_user values(null , '200' , '张三' , '男' , 22 , 45);
 59 
 60 -- 查询表信息
 61 select * from t_user
 62 
 63 select s_id , s_name , s_sex from student;
 64 -- 对查询结果起别名
 65 select s_name as name from student;
 66 
 67 -- 修改
 68 update t_user set sex = '女' , weight = weight - 10;
 69 
 70 update t_user set sex = '男' where name = '张三';
 71 
 72 -- 删除数据
 73 delete from t_user where id < 2;
 74 
 75 -- 对结果集去重
 76 select DISTINCT name , sex from t_user;
 77 
 78 -- where 
 79 select * from student where s_id = 1;
 80 -- 不等于 != <>
 81 select * from student where s_id <> 1;
 82 
 83 -- 对null的判断
 84 -- 用 = 是错的,要用is
 85 select * from student where s_name = null;
 86 select * from student where s_name is null;
 87 select * from student where s_name is not null;
 88 
 89 -- 逻辑运算符(AND优先级高于OR)
 90 select * from student where s_id = 1 and s_name = '赵雷' or s_sex = '男';
 91 -- 验证子查询中是否有结果
 92 select * from student where exists (select * from student where s_id = 1);
 93 
 94 -- in
 95 select * from student where s_id in (1,2,3,4);
 96 
 97 -- like
 98 -- _代表一个占位符 %代表不限
 99 select * from student where s_name like '赵_';
100 
101 -- all
102 select * from score where s_score > all(select s_score from score where s_score < 60);
103 
104 -- any
105 select * from score where s_score > any(select s_score from score where s_score < 60);
106 
107 -- limit(从0开始查询5条)
108 select * from student limit 0 , 5;
109 
110 -- order by(正序)
111 select * from score order by s_score;-- 默认 asc
112 select * from score order by s_score desc;-- 倒序

  1 -- 分组查询
  2 SELECT * FROM student;
  3 
  4 -- 男同学女同学各多少人
  5 -- GROUP BY
  6 -- HAVING 对分组之后的结果再进行筛选
  7 SELECT s_sex , count(*) FROM student GROUP BY s_sex HAVING COUNT(*)>4;
  8 SELECT AVG(age) FROM t_user GROUP BY name;-- AVG不计算NULL值
  9 
 10 -- 合并查询 UNION
 11 -- 自动去重
 12 SELECT * FROM student WHERE s_id = 01
 13 UNION
 14 SELECT * FROM student WHERE s_id = 05;
 15 -- 链接所有的结果集,不会去重 UNION ALL
 16 SELECT * FROM student WHERE s_id = 01
 17 UNION ALL
 18 SELECT * FROM student WHERE s_id = 05;
 19 
 20 -- CASE WHEN THEN ELSE END
 21 -- 所有CASE WHEN THEN 结束以后都要用END结为
 22 SELECT s_name , case s_sex WHEN '男' THEN '小男孩' WHEN '女' THEN '小女孩' ELSE '未知' END AS 'sex' FROM student;
 23 
 24 SELECT CASE WHEN s_name is null THEN '无名氏' ELSE s_name END AS 'name' FROM student;
 25 
 26 CREATE TABLE t_info as
 27 SELECT a.s_id,a.s_name,b.s_score,c.c_name from student a LEFT JOIN score b on a.s_id=b.s_id LEFT JOIN course c on c.c_id=b.c_id;
 28 
 29 SELECT s_id , s_name,
 30 sum(case c_name when '数学' then s_score end)as '数学',
 31 sum(case c_name when '语文' then s_score end)as '语文',
 32 sum(case c_name when '英语' then s_score end)as '英语'
 33 from t_info group by s_id;
 34 
 35 -- select @@global.sql_mode;
 36 -- set @@global.sql_mode='ONLY_FULL_GROUP_BY';
 37 -- set @@GLOBAL.sql_mode='';
 38 -- set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
 39 
 40 SELECT * FROM t_info;
 41 select s_name from (select * from t_info where s_score >= 60)as a group by s_id;
 42 
 43 select avg(s_score) from t_info;
 44 
 45 -- Left JOIN(以左边的表为主表)
 46 SELECT * from student a left join score b on a.s_id = b.s_id;
 47 
 48 -- RIGHT JOIN(以右边的表为主表)
 49 SELECT * from student a right join score b on a.s_id = b.s_id;
 50 
 51 -- inner JOIN(只选择两个表都有的公共部分)
 52 SELECT * from student a inner join score b on a.s_id = b.s_id;
 53 
 54 -- 创建用户
 55 CREATE USER 'easy'@'';
 56 
 57 INSERT INTO mysql.user(Host, User,  password, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'easy', PASSWORD('password'), '', '', '');
 58 
 59 select * from mysql.user;
 60 
 61 GRANT SELECT ON*.* TO 'easy'@'%' IDENTIFIED BY '123456';
 62 
 63 -- 修改密码
 64 set password for 'easy'@'localhost'=PASSWORD('abcdef');
 65 
 66 grant SELECT , INSERT ,UPDATE ,DELETE on hhr_data.student to 'easy'@'localhost' with grant option;
 67 
 68 grant update(s_id , s_name) on hhr_data.t_info to 'easy'@'localhost';
 69 
 70 -- 打印出张三老师负责的科目信息
 71 select c_id,c_name from course left join teacher on teacher.t_id = course.t_id where t_name = '张三';
 72 
 73 -- 学过张三老师课程的学生信息
 74 select s_name , s_birth , s_sex from student left join score on student.s_id = score.s_id left join course on course.c_id = score.c_id LEFT JOIN teacher on teacher.t_id = course.t_id where teacher.t_name = '张三'
 75 
 76 -- 查询出平均成绩最高的学生信息
 77 SELECT t1.* from student as t1 right JOIN(
 78 select score.s_id , AVG(score.s_score) as avgscore from score GROUP BY score.s_id ORDER BY avgscore DESC limit 0,1
 79 )as t2 on t1.s_id = t2.s_id;
 80 
 81 -- 查询出学过所有课程的学生信息
 82 select student.* from student,(
 83 select s_id , COUNT(c_id) as coursecount from score GROUP BY s_id) as t1
 84 where student.s_id = t1.s_id
 85 and t1.coursecount = (select count(*) from course);
 86 
 87 -- 查询出语文成绩比数学成绩高的学生信息
 88 select * from student where s_id in(
 89 select a.s_id from (
 90 select * from score where c_id =(select c_id from course where c_name='语文')) as a
 91 left join (
 92 select * from score where c_id =(select c_id from course where c_name='数学')) as b
 93 on a.s_id=b.s_id where a.s_score>b.s_score);
 94