1.SQL语言

SQL简介

SQL是(Structure Query Language)结构化查询语言的缩写，由IBM于20世纪70年代开发出来。

SQL使用入门

SQL分类

SQL分为3类

DDL（Data Difinition Language）

数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter。

DDL主要对数据库内部的对象进行创建、修改、删除等操作，他和DML的最大区别就是不对表内部的数据进行操作。DDL一般由数据库管理员使用，开发人员很少使用。

1.创建数据库

在数据库启动后输入以下命令连接到数据库

mysql -u root -p

其中-u后面紧跟数据库用户-p则表示用户密码，连接成功后通过create database name命令创建数据库，如创建数据库test1

create database test1;

如果想要知道系统中存在哪些数据库，可以通过以下命令查看

show database;

如果想要使用某一个数据库可以使用use命令进入,如选择数据库test1

use test1;

想要查看当前数据库中有哪些表可以使用如下命令

show tables;

2.删除数据库

如果想要删除某个数据库可以使用drop命令,比如删除test数据库：

drop database test;

数据库删除后，数据库下面的所有表都会被删除，所以在删除之前一定要仔细检查并做好相应的备份

3.创建表

在数据库中创建一张表的基本语法如下:

CREATE TABLE tablename(
	column_name_1 column_type_1 constraints,
	column_name_2 column_type_2 constraints,
	column_name_3 column_type_3 constraints,
	column_name_4 column_type_4 constraints,
	...
	column_name_n column_type_n constraints,
);

其中column_name是列的名字,column_type是列的数据类型,constraints是列的约束条件.

例如创建一个名为student的表,表中包括id(主键)、username(学生姓名)、password(密码)、birthday(出生日期)、age(年龄)

Create Table: CREATE TABLE `student` (
  `age` int(11) DEFAULT NULL,
  `id` varchar(30) NOT NULL,
  `username` varchar(60) NOT NULL,
  `passowrd` varchar(40) NOT NULL,
  `birthday` varchar(60) DEFAULT NULL,
   PRIMARY KEY (`id`)
) 

表创建完成后，想要查看表的定义可以通过desc命令

desc student;

虽然desc可以查看表的定义信息，但是不够全面，想要查看更为全面的表信息可以使用以下命令：

show create table student \G;

其中\G含义为让字段竖向排列

4.删除表

表的删除命令如下

DROP TABLE tablename;

例如要删除student表可以使用如下命令

DROP TABLE student;

5.修改表

对于已经创建号的表，如果要做一些表结构上的修改，可以先删除表，然后按照新的建表规则重新建表，但是这样就会涉及到重新载入数据的步骤，但是在实际应用中是不能将表直接删除的，比如表正在被系统使用，想要对表结构做一些动态的修改，可以通过以下方法

1.修改字段

表字段修改的命令格式为

ALTER TABLE tablename MODIFY column_name constraints;

比如要将student中的username字段修改为varchar(60)

ALTER TABLE student MODIFY username varchar(60) NOT NULL; 

2.删除字段

表字段的删除命令格式为

ALTER TABLE tablename DROP column_name;	

如要删除student表的username字段可以使用如下命令

ALTER TABLE student DROP username;

通过desc student来查看表信息

3.新增表字段

新增表字段的命令如下

ALTER TABLE tablename ADD column_name constraints;

如将删除的username重新添加可以使用如下命令

ALTER TABLE student ADD username varchar(60) NOT NULL;

查看表结构如下

desc stduent;

4.字段改名

修改表字段名命令为

ALTER　TABLE tablename CHANGE[COLUMN] old_col_name column_difinition [after | before col_name] 

比如将age改名为age1，同时修改字段类型为int(4)

ALTER TABLE student CHANGE age age1 int(4);

5.修改字段顺序

在字段增加和修改语法(ADD/CHANGE/MODIFY)中都有一个可选项first | after column_name,这个选项可以改变字段在表中的位置，ADD增加的字段默认加在表的最后位置，而CHANGE/MODIFY默认都不会改变字段的位置。

如修改字段age1，将它放在最后面

ALTER TABLE student MODIFY age int(4) after username;

6.修改表名

修改表名的语法如下

ALTER　TABLE　tablename RENAME[TO] new_tablename

如将student表名修改为user

ALTER　TABLE student RENAME user;

DML（Data Manipulation Language）

数据操纵语言，用于添加、修改、删除和查询数据库记录，并检查数据的完整性。常用的语句关键字包括delete、update、insert、select。

DML操作是指对表数据的操作，主要包括表记录的插入(insert)、更新(update)、删除(delete)、查询(select)，是开发人员日常最频繁的操作。

1.插入记录

表创建完成后就可以插入数据了，插入记录的基本语法如下：

INSERT INTO tablename (field1,field2,field3,...,fieldn) VALUES（value1,value2,value3,...,valuen）;

如向表user中插入一下记录: id为1，username为Tom，password为123456，birthday为2001-2-15，age1为19

INSERT INTO user (id,username,password,birthday,age1) VALUES(1,'Tom','123456','2021-2-15',19);

也可以不指定名称，但是values的顺序应该和字段排列顺序一致;

INSERT INTO user VALUES(2,'123456','1996-2-15','Jhon',24);

含可空字段、非空但含有默认值的字段、自增字段，可以不用在insert后面的字段列表里出现，values中只写对应字段的value。那些没写的字段会默认设置为NULL、默认值、自增的下一个数字。

例如只对id，username，password三个属性进行插入

INSERT INTO user (id,username,password) VALUES(3,'Green','123456');

通过查询命令来查看一下实际插入的值

SELECT * FROM user;

在Mysql中还可以一次插入多条记录，语法如下

INSERT INTO tablename (field1,field2,...,fieldn)
VALUES
(record1_value1,record1_value2,...,record1_valuen),
(record2_value1,record2_value2,...,record2_valuen),
...
(recordn_value1,recordn_value2,...,recordn_valuen)

每条记录通过逗号,分隔

2.更新记录

已经存在的记录值可以通过update进行修改，语法如下

UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE condition]; 

例如将user表中id为3的记录中的password从123456修改为654321

UPDATE user SET password = '654321' WHERE id='3';

在MySQL中update命令可以同时修改多个表的记录

UPDATE t1,t2,...,tn SET t1.field1 = value1, t2.field2 = value2,...,tn.fieldn = valuen [WHERE condition];

3.删除记录

删除记录可以通过delete命令，命令如下

DELETE　FROM　tablename [WHERE condition];

如将user表中id为3的记录删除

DELETE　FROM　user WHERE id = '3';

在MySQL中可以一次删除多个表的数据，语法如下

DELETE　t1,t2,...,tn FROM t1,t2,...,tn [WHERE condition];

4.查询记录

记录插入到数据库表中之后,就可以进行各种各样的查询，以满足用户的要求。查询语句基本语法如下

SELECT * FROM tablename [WHERE condition];

其中*表示将所有记录都查询出来

例如将user表中的全部数据查询出来

SELECT * FROM user;

其他查询方法

（1）查询不重复的记录

如果表中的数据去重复后显示出来，可以使用distinct关键字

SELECT DISTINCT age1 FROM user

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OnrPHeR1-1597043081286)(D:\学习笔记\mysql\MySQL深入浅出\SQL语言.assets\image-20200810112845651.png)]

（2）条件查询

若要查询满足一定条件的数据，可以通过where对记录进行过滤

例如要查询age1为24的记录

SELECT * FROM user WHERE age1 = 24;

以上例子使用了=运算符,除此之外还可以使用<、>、<=、>=、!=等比较运算符，还可以使用or、and等逻辑运算符。

（3）排序

我们经常会遇到将记录排序后取出的需求，这就需要使用到ORDER BY关键字，语法如下

SELECT　＊　FROM tablename [WHERE condition] [ORDER BY field [DESC | ASC],field2 [DESC | ASC],...,fieldn [DESC | ASC]];

其中DESC和ASC是排序关键字，DESC表示按照字段进行降序排列，ASC表示按照字段进行升序排列。如果不写关键字默认是升序排列

例如把user表中的记录按照升序排列

SELECT * FROM uer ORDER BY age1;

（4）分页查询

如过只查询表中记录的一部分可以使用LIMIT来完成，语法如下：

SELECT ... [LIMIT offset_start row_count];

其中offset_start表示起始偏移量row_count表示显示记录数

例如要查询从0开始的两条记录

SELECT * FROM user LIMIT 0,2;

（5）聚合

在很多情况下，用户需要进行一些汇总操作，比如同意一个公司或一个部门的人数，这时就需要用到将汇总操作，语法如下；

SELECT [field1,field2,...,fieldn] fun_name FROM tablename [WHERE where_condition] [GROUP BY field1,field2,...,fieldn] [WITH ROLLUP] [HAVING  where_condition];

其中参数说明如下：

fun_name：表示要做的聚合操作，也就是聚合函数，常用的有sum（求和）、count（*）（记录数）、max（最大值）、min（最小值）

GROUP BY：关键字表示要分类聚合的字段，比如要按照部门分类统计员工数量，那么部门分类就应该写在GROUP BY后面

WITH ROLLUP：是可选语法，表示是否对分类聚合后的结果再汇总。

HAVING：表示对聚合后的结果进行再过滤

例如要统计user表中的总人数

select count(1) from user;

再次基础上统计各班级的人数

select classno,count(1) from user group by classno;

即要统计个班人数，又要统计总人数

select classno,count(1) from user group by classno with rollup;

统计人数大于1的班级

select classno,count(1) from user group by classno having count(1)>1;

最后统计所有学生平均年龄、最大年龄和最小年龄

select avg(age) as '平均年龄',max(age) as '最大年龄',min(age) as '最小年龄' from user;

（6）表链接

当需要同时查询多张表上的内容是就需要通过表链接。表链接大致分为内链接和外链接

内链接和外链接的区别

内链接查出的是两张表互相匹配的内容，外链接会查出其他不匹配的记录。我们最常用的内链接。

比如要查询所有学生及学生所在的班级名称

select u.username,c.name from user u inner join clazz c on u.classno = c.id;

外链接又分为左连接和右链接

左连接：包括左表中的记录即使右表中没有和它匹配的记录。

右连接：包括右表中的记录即使左表中没有和它匹配的记录。

示例如下

select u.username,c.name from user u left join clazz c on  u.classno=c.id;

（7）子查询

在某些情况下，查询需要用到的条件是一个查询的结果。这时就需要用到子查询，用于子查询的主要关键字包括：in，not in、=、!=、exist、not exist等。

例如查询user中所以记录中班级在clazz表中存在的所有记录

select * from user where classno in(select id from clazz);

如子查询的结果只有一个in可以通过=代替

在某系而情况下，查询可以转化为表链接如

select u.* from user u,clazz c where u.classno = c.id;

（8）记录联合

我们经常会遇到这样的应用，需要将两个查询的结果合并起来显示，这时就需要使用 union和 union all关键字来实现，例如将user和clazz表中的班级编号联合起来显示

    select classno from user union all select id from clazz;

将结果去掉重复后显示如下

    select classno from user union select id from clazz;

DCL（Data Control Language）

数据控制语言，用于控制不同数据段直接的许可哈访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

DCL语句主要是DBA用来管理系统中的对象权限时使用的，一般的开发人员很少使用。

创建一个数据库用户z1，具有对sakila数据库中所有表的SELECT /INSERT权限：

grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

退出当前登录状态,并使用z1用户进行登录

exit 
mysql -uz1 -p123
use sakila
insert into test values(null,'Tom',24);

select * from test;

重新通过root用户登录,并通过以下命令,删除z1用户查询权限

 revoke insert on sakila.* from 'z1'@'localhost';

重新通过z1用户登录,并进行查询操作

use test

一个数据库用户z1，具有对sakila数据库中所有表的SELECT /INSERT权限：

grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

退出当前登录状态,并使用z1用户进行登录

exit 
mysql -uz1 -p123
use sakila
insert into test values(null,'Tom',24);

select * from test;

重新通过root用户登录,并通过以下命令,删除z1用户查询权限

 revoke insert on sakila.* from 'z1'@'localhost';

重新通过z1用户登录,并进行查询操作

use test

总结: grant命令用来授予权限, revoke命令用来撤销权限