sql及其规范
sql是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。
在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持sql。<1> 在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写；
<2> SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。
<3> 用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。
<4> 注释：单行注释：--
多行注释：/*......*/
<5>sql语句可以折行操作
<6> DDL,DML和DCL
数据库操作(DDL)
--1.创建数据库
create database [if not exists] db_name [character set xxx];
--2.查看数据库
show databases;查看所有数据库
show create database db_name;查看数据库创建方式
--3.修改数据库
alter database db_name [character set xxx];
--4.删除数据库
drop database [if exists] db_name;
--5.使用数据库
use db_name;-- 注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换
查看当前使用的数据库 select database();数据表操作
--表格操作
create table tb_name( --创建表格
name1 type[约束条件],
name2 type,
name3 type,
...
)
drop tb_name; --删除表格
alter table tb_name add name type[约束条件][first|after name1]--为表格添加一个字段
alter table tb_name drop name;--删除表格的一个字段
alter table tb_name modify name type[约束条件]--修改字段属性
alter table tb_name change old_name new_name type[约束条件];--修改字段名字
alter table tb_name add primary key(name);--把字段设为主键
alter table tb_name add unique(name);--把字段值设为不可重复
alter table tb_name drop primary key;--删除主键(主键字段非空且唯一,一张表只有一个主键)
alter table tb_name drop index_name;--删除唯一索引
rename tb_name to new_name;--为列表修改名字
show tables; --查看所有表格
show create table tb_name;--查看创建表格方式
desc tb_name;--查看表格格式
--表内容操作
insert into tb_name (name1,name2,name3) values (value1,value2,value3),(value1,value2,value3);--增加记录,可一次加多个
insert into tb_name set name1=value1,name2=value2,name3=value3 ;--添加一条记录
delete from tb_name where name=value;--删除name=value的记录
delete from tb_name;--删除所有记录
truncate table tb_name;--删除列表,并重新建立一张与原来名字一样的表
update tb_name set name=New_value where name=value;--修改name为value的记录
select * from tb_name;--查看所有记录
select [distinct] name1,name2 from tb_name;--查看字段为name1,name2的一列记录,distinct为剔除重复
--表数据操作
--准备表
CREATE TABLE ExamResult(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
JS DOUBLE ,
Django DOUBLE ,
OpenStack DOUBLE
);
INSERT INTO ExamResult VALUES (1,"yuan",98,98,98),
(2,"xialv",35,98,67),
(3,"alex",59,59,62),
(4,"wusir",88,89,82),
(5,"alvin",88,98,67),
(6,"yuan",86,100,55);
select name1 as name2 from tb_name;--把字段name1作为name2显示
select name as 名字,js+django+OpenStack as 总成绩 from ExamResult;
select name,js+10,Django+10,OpenStack+10 from ExamResult;
select name from ExamResult where js>80;--用where做选择
select name as 名字,js+django+OpenStack as 总成绩 from ExamResult where js+django+OpenStack>200;
-- where字句中可以使用：
-- 比较运算符：
-- > < >= <= <> !=
-- between 80 and 100 值在10到20之间
-- in(80,90,100) 值是10或20或30
-- like 'yuan%'
/*
pattern可以是%或者_，
如果是%则表示任意多字符，此例如唐僧,唐国强
如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
*/
-- 逻辑运算符
-- 在多个条件直接可以使用逻辑运算符 and or not
-- Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
-- select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
select name,js from ExamResult order by js desc;
select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)) as 总成绩 from ExamResult where name like 'a%' order by 总成绩 desc;
-- group by 分组查询：
-- 注意,按分组条件分组后每一组只会显示第一条记录
-- group by字句，其后可以接多个列名，也可以跟having子句,对group by 的结果进行筛选。
select name,sum(js) from ExamResult group by name having sum(js)>150;
/*
having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
<2>使用where语句的地方都可以用having进行替换
<3>having中可以用聚合函数，where中就不行。
*/
--聚合函数
-- COUNT(列名)：统计行的个数-- 注意:count(*)统计所有行; count(字段)不统计null值.
select count(name) from ExamResult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;
-- SUM(列名)：统计满足条件的行的内容和-- 注意：sum仅对数值起作用，否则会报错。
select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)) as 总成绩 from ExamResult;
-- AVG(列名):求平均数
select avg(ifnull(JS,0)) from ExamResult;
-- Max、Min:求最大最小数 -- 注意：null 和所有的数计算都是null，所以需要用ifnull将null转换为0！
select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) as 最高分 from ExamResult;
select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) as 最低分 from ExamResult;
-- 重点：Select from where group by having order by
-- Mysql在执行sql语句时的执行顺序：
-- from where select group by having order by
-- 分析:
select JS as JS成绩 from ExamResult where JS成绩 >70; ---- 不成功
select JS as JS成绩 from ExamResult having JS成绩 >90; --- 成功
-- (8) limit
SELECT * from ExamResult limit 1;
SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录
SELECT * from ExamResult limit 2,2;外键
--被关联的表称为主表
CREATE TABLE teacher(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR (20),
age INT ,
is_marriged boolean
);
INSERT INTO teacher(name,age,is_marriged) VALUES ("冰冰",12,0),
("丹丹",14,0),
("歪歪",22,0),
("姗姗",20,0),
("小雨",21,0);
-- +----+------+------+-------------+
-- | id | name | age | is_marriged |
-- +----+------+------+-------------+
-- | 1 | 冰冰 | 12 | 0 |
-- | 2 | 丹丹 | 14 | 0 |
-- | 3 | 歪歪 | 22 | 0 |
-- | 4 | 姗姗 | 20 | 0 |
-- | 5 | 小雨 | 21 | 0 |
-- +----+------+------+-------------+
--关联的表称为子表
CREATE TABLE Student(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
charger_id TINYINT,
foreign key (charger_id) references teacher(id) --创建表时添加外键
) ;
--切记:作为外键一定要和关联主键的数据类型保持一致
--在表外部添加外键
alter table student ADD CONSTRAINT teacher_fk_stu FOREIGN KEY (charger_id) REFERENCES teacher(id);--teacher_fk_stu为自定义外键名,可以通过desc student查看
alter table student drop foreign key teacher_fk_stu;--删除外键
INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",1),
("alvin4",3),
("alvin5",1),
("alvin6",3),
("alvin7",2);
-- +----+--------+------------+
-- | id | name | charger_id |
-- +----+--------+------------+
-- | 1 | alvin1 | 2 |
-- | 2 | alvin2 | 4 |
-- | 3 | alvin3 | 1 |
-- | 4 | alvin4 | 3 |
-- | 5 | alvin5 | 1 |
-- | 6 | alvin6 | 3 |
-- | 7 | alvin7 | 2 |
-- +----+--------+------------+
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update,例如
insert into student values ('alvin8',6);--会失败,因为teacher中id值没有6的
update student set charger_id=6 where name='alvin6';--一样会失败
--外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对
-- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的
-- on update/on delete子句,例如
delete from teacher where id=2;--会失败,因为student中有2的学生,如果要删除只能先更改2的学生的charger_id
update student set charger_id=1 where charger_id=2;--此时上面那条命令才会执行
--除了上述的外键还有
-----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
-----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除--------
alter table student add constraint cas_fk foreign key (charger_id) references teacher(id) on delete cascade;
--执行
delete from teacher where id=2;
--teacher中id等于2与student中charger_id等于2的记录都会被删除
--set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null
-- 要注意子表的外键列不能为not null
alter table student add constraint FOREIGN KEY (charger_id) REFERENCES teacher(id) ON DELETE SET NULL;
--执行
delete from teacher where id=3;
--teacher中id等于3的记录被删除,student中charger_id等于3的值变为null
------Restrict方式 :拒绝对父表进行删除更新操作(了解)
------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
-- 进行update/delete操作（了解）多表查询
--准备表
create table employee(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int
);
insert into employee(emp_name,age,dept_id) values
('A',19,200),
('B',26,201),
('C',30,201),
('D',24,202),
('E',20,200),
('F',38,204);
-- +--------+----------+------+---------+
-- | emp_id | emp_name | age | dept_id |
-- +--------+----------+------+---------+
-- | 1 | A | 19 | 200 |
-- | 2 | B | 26 | 201 |
-- | 3 | C | 30 | 201 |
-- | 4 | D | 24 | 202 |
-- | 5 | E | 20 | 200 |
-- | 6 | F | 38 | 204 |
-- +--------+----------+------+---------+
create table department(
dept_id int,
dept_name varchar(100)
);
insert into department values
(200,'人事部'),
(201,'技术部'),
(202,'销售部'),
(203,'财政部');
-- +---------+-----------+
-- | dept_id | dept_name |
-- +---------+-----------+
-- | 200 | 人事部 |
-- | 201 | 技术部 |
-- | 202 | 销售部 |
-- | 203 | 财政部 |
-- +---------+-----------+
-------------------------------------连接查询--------------------------------------------
--1.笛卡尔积查询
select * from employee,department;--会显示出所有的组合,一共m*n个记录
-- +--------+----------+------+---------+---------+-----------+
-- | emp_id | emp_name | age | dept_id | dept_id | dept_name |
-- +--------+----------+------+---------+---------+-----------+
-- | 1 | A | 19 | 200 | 200 | 人事部 |
-- | 1 | A | 19 | 200 | 201 | 技术部 |
-- | 1 | A | 19 | 200 | 202 | 销售部 |
-- | 1 | A | 19 | 200 | 203 | 财政部 |
-- | 2 | B | 26 | 201 | 200 | 人事部 |
-- | 2 | B | 26 | 201 | 201 | 技术部 |
-- | 2 | B | 26 | 201 | 202 | 销售部 |
-- | 2 | B | 26 | 201 | 203 | 财政部 |
-- | 3 | C | 30 | 201 | 200 | 人事部 |
-- | 3 | C | 30 | 201 | 201 | 技术部 |
-- | 3 | C | 30 | 201 | 202 | 销售部 |
-- | 3 | C | 30 | 201 | 203 | 财政部 |
-- | 4 | D | 24 | 202 | 200 | 人事部 |
-- | 4 | D | 24 | 202 | 201 | 技术部 |
-- | 4 | D | 24 | 202 | 202 | 销售部 |
-- | 4 | D | 24 | 202 | 203 | 财政部 |
-- | 5 | E | 20 | 200 | 200 | 人事部 |
-- | 5 | E | 20 | 200 | 201 | 技术部 |
-- | 5 | E | 20 | 200 | 202 | 销售部 |
-- | 5 | E | 20 | 200 | 203 | 财政部 |
-- | 6 | F | 38 | 204 | 200 | 人事部 |
-- | 6 | F | 38 | 204 | 201 | 技术部 |
-- | 6 | F | 38 | 204 | 202 | 销售部 |
-- | 6 | F | 38 | 204 | 203 | 财政部 |
-- +--------+----------+------+---------+---------+-----------+
--2.内连接
select * from employee,department where employee.dept_id=department.dept_id;
--或者
select * from employee inner join department on employee.dept_id=department.dept_id;
-- +--------+----------+------+---------+---------+-----------+
-- | emp_id | emp_name | age | dept_id | dept_id | dept_name |
-- +--------+----------+------+---------+---------+-----------+
-- | 1 | A | 19 | 200 | 200 | 人事部 |
-- | 2 | B | 26 | 201 | 201 | 技术部 |
-- | 3 | C | 30 | 201 | 201 | 技术部 |
-- | 4 | D | 24 | 202 | 202 | 销售部 |
-- | 5 | E | 20 | 200 | 200 | 人事部 |
-- +--------+----------+------+---------+---------+-----------+
--也可以进行复合条件筛选(选出年龄大于25岁的员工)
select employee.emp_name,employee.age,department.dept_name from employee,department
where employee.dept_id=department.dept_id
and age>25;
-- +----------+------+-----------+
-- | emp_name | age | dept_name |
-- +----------+------+-----------+
-- | B | 26 | 技术部 |
-- | C | 30 | 技术部 |
-- +----------+------+-----------+
--3.外连接
--左连接:会把左边的表全部显示出来,没有的值用null代替
select * from employee left join department on department.dept_id=employee.dept_id;
-- +--------+----------+------+---------+---------+-----------+
-- | emp_id | emp_name | age | dept_id | dept_id | dept_name |
-- +--------+----------+------+---------+---------+-----------+
-- | 1 | A | 19 | 200 | 200 | 人事部 |
-- | 5 | E | 20 | 200 | 200 | 人事部 |
-- | 2 | B | 26 | 201 | 201 | 技术部 |
-- | 3 | C | 30 | 201 | 201 | 技术部 |
-- | 4 | D | 24 | 202 | 202 | 销售部 |
-- | 6 | F | 38 | 204 | NULL | NULL |
-- +--------+----------+------+---------+---------+-----------+
--右连接:会把右边的表全部显示出来,没有的值用null代替
select * from employee right join department on department.dept_id=employee.dept_id;
-- +--------+----------+------+---------+---------+-----------+
-- | emp_id | emp_name | age | dept_id | dept_id | dept_name |
-- +--------+----------+------+---------+---------+-----------+
-- | 1 | A | 19 | 200 | 200 | 人事部 |
-- | 2 | B | 26 | 201 | 201 | 技术部 |
-- | 3 | C | 30 | 201 | 201 | 技术部 |
-- | 4 | D | 24 | 202 | 202 | 销售部 |
-- | 5 | E | 20 | 200 | 200 | 人事部 |
-- | NULL | NULL | NULL | NULL | 203 | 财政部 |
-- +--------+----------+------+---------+---------+-----------+
--3.全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
-- mysql不支持全外连接 full JOIN
-- mysql可以使用此种方式间接实现全外连接
select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
UNION
select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
-- +--------+----------+------+---------+---------+-----------+
-- | emp_id | emp_name | age | dept_id | dept_id | dept_name |
-- +--------+----------+------+---------+---------+-----------+
-- | 1 | A | 19 | 200 | 200 | 人事部 |
-- | 2 | B | 26 | 201 | 201 | 技术部 |
-- | 3 | C | 30 | 201 | 201 | 技术部 |
-- | 4 | D | 24 | 202 | 202 | 销售部 |
-- | 5 | E | 20 | 200 | 200 | 人事部 |
-- | NULL | NULL | NULL | NULL | 203 | 财政部 |
-- | 6 | F | 38 | 204 | NULL | NULL |
-- +--------+----------+------+---------+---------+-----------+
-- 注意 union与union all的区别：union会去掉相同的纪录
---------------------------------------------子查询----------------------------------------------------
-- 子查询是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
-- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
-- 还可以包含比较运算符：= 、 !=、> 、<等
--1.IN
--查询employee表，但dept_id必须在department表中出现过
select * from employee where dept_id in (select dept_id from department);
-- +--------+----------+------+---------+
-- | emp_id | emp_name | age | dept_id |
-- +--------+----------+------+---------+
-- | 1 | A | 19 | 200 |
-- | 2 | B | 26 | 201 |
-- | 3 | C | 30 | 201 |
-- | 4 | D | 24 | 202 |
-- | 5 | E | 20 | 200 |
-- +--------+----------+------+---------+
--2. 带比较运算符的子查询 =、!=、>、>=、<、<=、<>
select distinct dept_name from department where dept_id in (select dept_id from employee where age>=25);
-- +-----------+
-- | dept_name |
-- +-----------+
-- | 技术部 |
-- +-----------+
--3.带EXISTS关键字的子查询
-- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
-- 而是返回一个真假值。Ture或False
-- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
select emp_name,age from employee where exists (select dept_id from department where dept_id=205);
-- Empty set (0.00 sec)
--还可以用于复制表
create table ABC(select * from employee);
select * from ABC;
-- +--------+----------+------+---------+
-- | emp_id | emp_name | age | dept_id |
-- +--------+----------+------+---------+
-- | 1 | A | 19 | 200 |
-- | 2 | B | 26 | 201 |
-- | 3 | C | 30 | 201 |
-- | 4 | D | 24 | 202 |
-- | 5 | E | 20 | 200 |
-- | 6 | F | 38 | 204 |
-- +--------+----------+------+---------+
desc ABC;
-- +----------+-------------+------+-----+---------+-------+
-- | Field | Type | Null | Key | Default | Extra |
-- +----------+-------------+------+-----+---------+-------+
-- | emp_id | int(11) | NO | | 0 | |
-- | emp_name | varchar(50) | YES | | NULL | |
-- | age | int(11) | YES | | NULL | |
-- | dept_id | int(11) | YES | | NULL | |
-- +----------+-------------+------+-----+---------+-------+索引
-- 索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
--
-- 索引优化应该是对查询性能优化最有效的手段了。
-- 索引能够轻易将查询性能提高好几个数量级。
-- 索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
--
-- 索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！
--创建表时
--语法：
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
--------------------------------
--创建普通索引例子
create table emp1(
id int,
name varchar(25),
index index_name(name)--index_name是自定义索引名,如果不写默认是括号内的字段名
--key index_name(name)
);
--创建唯一索引例子
create table emp2(
id int,
name varchar(25),
unique index index_name(name)
);
--创建全文索引示例：
CREATE TABLE emp3 (
id INT,
name VARCHAR(30) ,
resume VARCHAR(50),
FULLTEXT INDEX index_resume (resume)
);
--创建多列索引示例：
CREATE TABLE emp4 (
id INT,
name VARCHAR(30) ,
resume VARCHAR(50),
INDEX index_name_resume (name, resume)
);
---------------------------------
---添加索引
---CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
---ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
create unique index index_name on emp1 (name desc);
alter table emp2 add spatial index index_name (name);
-- 删除索引
语法：DROP INDEX 索引名 on 表名
drop index index_name on emp1;
扫一扫
111万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
