Mysql常用SQL语句

SQL语句：

DCL：数据控制语言，用来定义访问权限和安全等级；

1.创建用户

lCREATE USER 用户名@IP地址 IDENTIFIED BY '密码';

用户只能在指定的IP地址上登录

lCREATE USER 用户名@'%' IDENTIFIED BY '密码';

用户可以在任意IP地址上登录

 

2.给用户授权

lGRANT 权限1,……,权限n ON 数据库.* TO 用户名@IP地址;

给用户分派在指定的数据库上的指定权限

例如：GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

给user1用户分派在mydb1数据库上的CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT权限

lGRANT ALL ON 数据库.* TO 用户名@IP地址;

给用户分派指定数据库上的所有权限

 

3.撤销授权

lREVOKE 权限1,……,权限n ON 数据库.* FROM 用户名@IP地址;

撤销指定用户在指定数据库上的指定权限

例如：REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

撤销user1用户在mydb1数据库上的CREATE、ALTER、DROP权限

 

4.查看权限

lSHOW GRANTS FOR 用户名@IP地址;

查看指定用户的权限

 

5.删除用户

lDROP USER 用户名@IP地址;

 

 

DDL：数据定义语言，操作数据库数据表，对数据库以及数据表进行增、删、改

1.创建school数据库

Create database school;

 

2.查看数据库

Show databases;

 

3.切换数据库

use school;

 

4.删除数据库(在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。)

drop database school;

 

5.修改数据库编码

Alter database school character set utf8;

 

6.创建student表，包含学号，姓名，年龄，性别，院系

Create table student(

Number char(10) Primary key,

Name varchar(30) not null,

Age int,

Sex enum('0','1')

)

常用的约束的类型：

①not null：非空约束，指定某列不为空

②unique：唯一约束，指定某列和几列组合的数据不能重复

③primary key：主键约束，指定某列的数据不能重复、唯一、非空

④foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据

 

7.查看数据表

show tables;

 

8.删除student表

Drop table student;

 

9.查看student表结构

Desc student;

 

 10.为student表添加career列

Alter table student add(

Career char(50)

);

 

11.修改student表career列数据类型为varchar(50)（如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据）

 

Alter table student modify career varchar(50);

 

12.修改student表career列名为major新列名数据类型为varchar(40)

Alter table student change career major varchar(40);

 

13.删除student表major列

Alter table student drop major;

 

14.修改student表名为pupil

Alter table student rename to pupil;

 

DML：数据操作语言，用来定义数据库记录（数据）

1.向pupil表插入数据

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

Insert into pupil(number,name,age,sex) values(‘001’,’张三’,18,’1’);

 

2.修改pupil表中张三年龄为20

UPDATE 表名 SET 列名1=列值1,列名2=列值2,... [WHERE 条件]

Update pupil set age=20 where name=’张三’;

 

3.删除表中王燕的信息

lDELETE FROM 表名 [WHERE 条件];

Delete from pupil where name=’王燕’;

 

DQL：数据查询语言，用来查询记录（数据）；

DQL语句只会对数据库查询，不会修改数据。

基本查询

SELECT * FROM 表名;

其中“*”表示所有列

 

查询指定列

SELECT 列1[,列2,……列n] FROM 表名;

 

完全重复的记录只显示一次

当查询结果中的多行记录完全一样时，只显示一行，一般用于查询某个字段中一共有几种类型的值。

SELECT DISTINCT * | 列1[,列2,……,列n] FROM 表名;

 

列运算

数量类型的列可以做加、减、乘、除运算,字符串做算术运算时，会被当做0来进行运算，在字符串中+号不代表拼接

SELECT  *,sal*1.5  FROM  emp;

 

字符串类型可以做连续运算

SELECT  CONCAT('$',sal)  FROM  emp;

 

转换NULL值

有时需要把NULL转换成其他值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这是希望把NULL当做0来运算。

SELECT IFNULL(comm,0)+1000 FROM emp;

 

 

给列起别名

当使用列运算后，查询出的结果集中的列名称不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了。其中AS可以省略

SELECT IFNULL(comm,0)+1000 AS 奖金 FROM emp;

 

 

2.条件控制

条件查询

与UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录。

SELECT empno,ename,sal,comm FROM emp WHERE sal > 1000 AND comm IS NOT NULL;

SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;

SELECT empno,ename,job FROM emp WHERE job IN('经理','董事长');

 

模糊查询

当你想查询姓张，并且姓名一共两个字的员工时，就可以使用模糊查询

模糊查询需要使用运算符：LIKE，其中匹配一个任意字符，注意，只匹配一个字符而不是多个。

上面语句查询的是姓张，名字由两个字组成的员工。

SELECT * FROM emp WHERE ename LIKE '张_';

下划线“_”可以匹配1个字符，如果要匹配0-n个字符，需要用“%”;

查询名字结尾是带“刚”字的员工。

SELECT * FROM emp WHERE ename LIKE ‘%刚’;

 

3.排序查询

升序

按sal排序，ASC升序，DESC降序，其中ASC是可以省略的

SELECT * FROM emp ORDER BY sal ASC;

 

降序

按comm降序排序查询

SELECT * FROM emp ORDER BY comm DESC;

 

使用多列作为排序条件

使用sal升序排序，如果sal相等，再按照comm降序排序

SELECT * FROM emp ORDER BY sal ASC, comm DESC;

 

4.聚合函数

聚合函数用来做某列的纵向运算

COUNT()函数

SELECT COUNT(*) FROM emp;

计算emp表中所有列都不为NULL的记录的行数

SELECT COUNT(comm) FROM emp;

计算emp表中comm列不为NULL的记录的行数

MAX()函数

SELECT MAX(sal) FROM emp;

查询最高工资

MIN()函数

SELECT MIN(sal) FROM emp;

查询最低工资

SUM()函数

SELECT SUM(sal) FROM emp;

查询所有员工工资的总和

AVG()函数

SELECT AVG(sal) FROM emp;

查询平均工资

 

5.分组查询

l分组查询是把记录使用某一列进行分组，然后查询组信息。

例如：查看所有部门的记录数。

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

使用deptno分组，查询部门编号和每个部门的记录数

SELECT job,MAX(sal) FROM emp GROUP BY job;

使用job分组，查询每种工作的最高工资

l组条件

以部门分组，查询每组记录数，条件为记录数大于3

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

 

6.LIMIT子句

lLIMIT用来限定查询结果的起始行，以及总行数。

例如：查询起始行为第5行，一共查询3行记录

SELECT * FROM emp LIMIT 4,3;

其中4表示从第5行开始，其中3表示一共查询3行。即第5/6/7行记录。

l分页查询中计算起始页的公式：（当前页-1）*每页记录数；

例如：每页显示10条记录，查询第3页

SELECT * FROM emp LIMIT 20，10;