MySQL基础——SQL语句

目录

1.SQL通用语法

2.SQL分类

3 DDL

3.1数据库操作

3.1.1查询

3.1.2创建

3.1.3删除

3.1.4使用

3.2表操作

3.2.1查询

3.2.2创建

3.2.3数据类型

3.2.4表修改（alter打头）

3.2.5表删除（drop/truncate打头）

3.3 DDL总结

3.3.1 DDL-数据库操作

3.3.2.DDL-表操作

4 DML

4.1添加数据（INSERT)

4.2修改数据(UPDATE)

4.3删除数据（DELETE)

4.4 DML总结

5 DQL（查询）

5.1基本查询

5.2条件查询（where）

5.3聚合函数

5.4 分组查询（group by）

5.5排序查询（order by）

5.6 分页查询（limit）

5.7 DQL语句总结

6 DCL语句

6.1用户管理

6.2权限控制

1.SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。

2. SQL语句可以使用空格/缩进来增强语句的可读性。

3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4.注释:

（1）单行注释:--注释内容或#注释内容(MySQL特有)

（2）多行注释:/*注释内容*/

2.SQL分类

SQL语句，根据其功能，主要分为四类:DDL、DML、DQL、DCL。

3 DDL

对数据库、表、字段的定义操作。

3.1数据库操作

3.1.1查询

查询所有数据库：SHOW DATABASES;

例：输入，就可查询当前数据库服务器中所有的数据库，包括我刚刚新建的两个。

查询当前正在使用的数据库：SELECT DATABASE(); #注意括号()不能少！

3.1.2创建

CREATE DATABASE [IF NOT EXISTS]数据库名「 DEFAULT CHARSET字符集][COLLATE 排序规则];

例1：输入create database itcast;

就会创建好名为itcas的数据库：

例2：输入CREATE DATABASE if not exists ithe default charset utf8mb4;

3.1.3删除

DROP DATABASE [ IF EXISTS]数据库名;

例：输入drop DATABASE ithe

然后查询：show DATABASEs

发现就没有ithe这个表了

3.1.4使用

USE 数据库名;

切换到指定的这个数据库，进行使用。

例：输入use library

再查询：SELECT DATABASE()

结果为：

3.2表操作

3.2.1查询

查询当前数据库所有表

也可以用use切换指定数据库，再查询。

查询表结构

DESC表名;

查询指定表的建表语句

SHOW CREATE TABLE表名;

3.2.2创建

逗号分离，最后一个字段没有逗号。通过comment加上对应字段的注释。

示例：

可以用desc+表名 查询表结构：

3.2.3数据类型

（1）数值类型

（2）字符串类型

VARCHAR——VS——CHAR

  1）VARCHAR类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。

  有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。

  2）CHAR类型用于存储固定长度字符串：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除字符串中的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上)。

  同时，CHAR值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

如：

用户名username varchar(50)

性别gender char(1)

（3）日期时间类型

没有负数，就要用无符号的，加上关键字unsigned。

例：设计一张员工信息表，要求如下:

1.编号（纯数字)

2.员工工号(字符串类型，长度不超过10位)

3.员工姓名（字符串类型，长度不超过10位)

4.性别（男/女，存储一个汉字)

5.年龄（正常人年龄，不可能存储负数)

6.身份证号（二代身份证号均为18位，身份证中有X这样的字符)

7.入职时间（取值年月日即可)

代码如下：

create table emp(id int comment '编号',empid varchar(10) comment '员工工号',empname varchar(10) comment '员工姓名',sex char(1) comment '性别',age tinyint unsigned comment'年龄',idcard char(18) comment'身份证号',entime date comment'入职时间')

3.2.4表修改（alter打头）

（1）添加字段add

ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT注释][约束];

案例:

为emp表增加一个新的字段“昵称”为nickname，类型为varchar(20)

代码：

alter table emp add nickname varchar(20) comment'昵称'

查询可知已成功添加：

（2）修改字段

1）修改字段数据类型（modify）

ALTER  TABLE 表名 MODIFY 字段名 新数据类型(长度);

2）修改字段名和字段类型（change）

ALTER TABLE表名CHANGE旧字段名 新字段名 类型(长度)[COMMENT注释][约束];

案例:

将emp表的nickname字段修改为username，类型为varchar(30)

代码：在添加字段的代码上修改就是将add改为change，原本一个字段名，改为包含旧和新的两个字段名。

alter table emp change nickname username varchar(20) comment'昵称'

（3）修改表名（rename to）

ALTER TABLE 表名 RENAME TO 新表名;

例：改emp为employee

代码：

alter table emp rename to employee

（4）删除字段

ALTER TABLE表名DROP字段名;

3.2.5表删除（drop/truncate打头）

（1）删除表

DROP TABLE [ IF EXISTS]表名;

（2）删除指定表，并重新创建该表。

TRUNCATE TABLE表名; （数据没有了只有一张空表了）

例：drop table if exists employee

查询显示无employee表了。

3.3 DDL总结

3.3.1 DDL-数据库操作

SHOW DATABASES;

CREATE DATABASE数据库名;

USE数据库名;

SELECT DATABASE();

DROP DATABASE数据库名;

3.3.2.DDL-表操作

SHOW TABLES ;

CREATE TABLE表名(字段字段类型，字段字段类型);

DESC表名;

SHOW CREATE TABLE表名;

ALTER TABLE表名 ADD/MODIFY/CHANGE/DROP/RENAME TO ...;

DROP TABLE表名;

4 DML

MySQL图形化工具，DataGrip>Navicat

但是DataGrip是收费的，有30天免费，可以申请学生邮箱！！！

链接数据库成功，跟我Navicat里面是一样的了。安装并链接好DataGrip后，学习DML。

DataGrip中的schema = database

在上面+操作，下面自己生成SQL语句，真香。

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

4.1添加数据（INSERT)

（1）给指定字段添加数据

INSERT INTO表名(字段名1,字段名2,.….)VALUES(值1,值2,....);

（2）给全部字段添加数据（不用写字段名了）

INSERT INTO表名VALUES(值1,值2,...);

（3）批量添加数据（用括号和逗号分隔）

INSERT INTO表名(字段名1,字段名2,...）)VALUES(值1，值2,...),(值1，值2...).(值1，值2,...);

INSERT INTO表名VALUES(值1,值2,...).(值1,值2,...), (值1,值2,...);

注意：

·插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

·字符串和日期型数据应该包含在引号中。

·插入的数据大小，应该在字段的规定范围内。

例：

#指定全部字段insert into employee(id, empid, empname,gender,age, idcard, entime) values(1,'1','zhg','n',10,'1234567889','2000-01-12');#指定部分字段
insert into employee (id, empid) values(3,'3');#不用指定，插入全部字段
insert into employee values(2,'2','zhg','n',10,'1234567889','2000-01-12');#批量插入insert into employee values(4,'4','zhg','n',10,'1234567889','2000-01-12'),                            (5,'5','zhg','n',10,'1234567889','2000-01-12'),                            (6,'6','zhg','n',10,'1234567889','2000-01-12'),                            (7,'7','zhg','n',10,'1234567889','2000-01-12');

结果如下：

4.2修改数据(UPDATE)

修改“表名”这个表总的字段数据信息：

UPDATE 表名 SET字段名1=值1 ,字段名2=值2,....[ WHERE条件];

例：

#将id=1的员工名字改为xixiupdate employee set empname='xixi' where id = 1;#将所有员工入职时间改为2008-02-13update employee set entime='2008-02-13'

修改结果如下：

4.3删除数据（DELETE)

DELETE FROM表名[WHERE条件]

注意:

·DELETE语句的条件可有可无，如果没有条件，则会删除整张表的所有数据。

·DELETE语句不能删除某一个字段的值(可以使用URDATE)。

例：

#删除id = 1的员工

delete from employee where id = 1

4.4 DML总结

1．添加数据

INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,..)[，,(值1,值2,...)...];

2．修改数据

UPDATE 表名 SET字段1=值1，字段2=值2[ WHERE条件];

3．删除数据

DELETE  FROM 表名 [WHERE条件];

5 DQL（查询）

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。查询关键字:SELECT

·DQL-语法

SELECT

        字段列表

FROM

        表名列表

WHERE

        条件列表

GROUP BY

        分组字段列表

HAVING

        分组后条件列表

ORDER BY

        排序字段列表

LIMIT

        分页参数

 # 新建emp表

create table emp(id int comment '编号',workno varchar(10) comment '员工工号',name varchar(10) comment '员工姓名',gender char comment '性别',age tinyint unsigned comment'年龄',idcard char(18) comment'身份证号',workaddress varchar(50) comment '工作地址',endate date comment'入职时间');

# 解决DataGrip对出现中文报错,使用如下语句,否则name和gender中只能是英文不能是中文。alter table emp convert to charset utf8;#插入数据

insert into emp (id,workno,name,gender,age,idcard , workaddress,endate)values (1,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),(2,'2','张无忌','男',18,'123456789012345670','北京','2005-09-01'),(3,'3','韦一笑','男',38,'123456789712345670','上海','2005-08-01'),(4,'4','赵敏','女',18,'123456757123845670','北京','2009-12-01'),(5,'5','小昭','女',16,'123456769012345678 ','上海','2007-07-01'),(6,'6','杨道','男',28,'12345678931234567X','北京','2006-01-01 '),(7,'7','范瑶','男',40,'123456789212345670','北京','2005-05-01 '),(8,'8','黛绮丝','女',38,'123456157123645670','天津','2015-05-01 '),(9,'9','范凉凉','女',45,'123156789012345678','北京','2010-04-01 '),(10,'10','陈友谅','男',53,'123456789012345670','上海','2011-01-01'),(11,'11','张士诚','男',55,'123567897123465670','江苏','2015-05-01'),(12,'12','常遇春','男',32,'123446757152345670','北京','2004-02-01 '),(13,'13','张三丰','男',88,'123656789012345678','江苏','2020-11-01 '),(14,'14','灭绝','女',65,'123456719012345670','西安','2019-05-01 '),(15,'15','胡青牛','男',70,'12345674971234567X','西安','2018-04-01 '),(16,'16 ','周芷若','女',18, null,'北京','2012-06-01 ');

5.1基本查询

查询例：

#查询多个字段

select name,workno,workaddress adr from emp;

#查询员工地址不能重复并起别名

select distinct workaddress adr from emp;

（distinct好像只能紧跟select）

5.2条件查询（where）

语法：SELECT 字段列表 FROM 表名 WHERE 条件列表;

案例:

一个下划线_代表一个字符，%匹配任意个字符。

#查询姓名为两个字的员工

select * from emp where name like'__';

#查询身份证最后一位为X的员工

select * from emp where idcard like'%X';

5.3聚合函数

将一列数据作为一个整体，进行纵向计算。

常见聚合函数：

语法：SELECT聚合函数(字段列表)FROM表名;

注意：null值不参与所有聚合函数运算。

案例：

5.4 分组查询（group by）

有些情况不分组也可以进行聚合运算，只有当前数据记录可以进行该纵向计算。

1.语法

SELECT字段列表FROM表名[ WHERE条件 ] GROUP BY分组字段名[HAVING分组后过滤条件];

2. where与having区别

执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。判断条件不同: where不能对聚合函数进行判断，而having可以。

注意

·执行顺序: where >聚合函数>having 。

·分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

案例：查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于1的工作地址。

分析：

分组前过滤：年龄小于45的员工——where

分组：工作地址——group by

分组后过滤：员工数量大于等于3的工作地址——having

代码：

select workaddress,count(id) countid from emp where age<45 group by workaddress having count(id) >=1;

分组后，有些字段就是不能同时输出了。

5.5排序查询（order by）

1.语法

SELECT字段列表FROM表名

ORDER BY字段1排序方式1，字段2排序方式2;

2．排序方式

ASC：升序（默认值，所以asc可以省略）

DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

案例：根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序.

代码：

select * from emp order by age,endate desc;

5.6 分页查询（limit）

（1）语法：

SELECT字段列表FROM 表名LIMIT起始索引,查询记录数;

（2）注意：

·起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。

·分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

·如果查询的是第一页数据，起始索引0可以省略，直接简写为limit 10。

（3）案例：

#查询第1页员工数据，每页展示10条记录

select * from emp limit 0,10;select * from emp limit 10; #起始页为0时可省略，效果同上

#查询第2页员工数据，每页展示10条记录#起始索引 =(页码-1)*页展示记录数 =（2-1）*10 =10，所以这里其实索引为10

select * from emp limit 10,10;

5.7 DQL语句总结

6 DCL语句

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。因此开发人员不用掌握，主要是DBA数据库管理员进行操作。

6.1用户管理

1．查询用户

USE mysql;

SELECT * FROM user;

2.创建用户

CREATE USER ‘用户名'@'主机名’IDENTIFIED BY‘密码’;

3．修改用户密码

ALTER USER  '用户名'@'主机名’ IDENTIFIED WITH mysql_native_password BY‘新密码’;

4．删除用户

DROP USER '用户名'@'主机名';

6.2权限控制

1．查询权限

SHOW GRANTS FOR‘用户名'@'主机名’;

2.授予权限

GRANT权限列表ON数据库名.表名TO‘用户名'@'主机名';

3．撤销权限

REVOKE 权限列表ON数据库名.表名FROM‘'用户名'@'主机名';