mysql数据库常考的sql语句_mysql数据库常用sql语句

mysql数据库常用sql语句

DDL操作表

复制表

CREATE TABLE STU LIKE STUDENT;

创建表

CREATE TABLE TB(

列名1 数据类型1，

列名2 数据类型2，

。。。

列名n 数据类型n

);

常用数据类型：

*age int

*score double(5,2)小数最多5位，小数点后面保留2位，最大值为999.99

date:日期，只包含年月日，yyyy-MM-dd

datetime：日期，包含年月日，时分秒 yyyy-MM-dd HH:mm:ss

timestamp:时间戳类型包含年月日，时分秒 yyyy-MM-dd HH:mm:ss，

如果不给这个字段赋值或赋值为空则使用当前的系统时间，来自动赋值

varchar:字符串

name varchar(20);姓名最大20个字符

CREATE TABLE STUDENT(

ID INT,

NAME VARCHAR(32),

AGE INT,

SCORE DOUBLE(4,1),

BIRTHDAY DATE,

INSERT_TIME TIMESTAMP

);

R(Retrieve)

查询某个数据库中所有的表

SHOW TABLES;

查询表结构

DESC 表名；

U(update)

1,修改表名

ALTER TABLE 表名 RENAME TO 新表名

2,修改表字符集,查看字符集

SHOW CREATE TABLE 表名;

ALTER TABLE 表名 CHARACTER SET UTF8

3,添加一列

ALTER TABLE 表名 ADD 列名 数据类型；

4,修改列名称 类型

ALTER TABLE 表名 CHANGE 修改前名称 修改后名称 修改后数据类型;

ALTER TABLE 表名 MODIFY 列名 数据类型；//只改类型不改名

5,删除列

ALTER TABLE 表名 DROP 列名

D(Delete)

删除表

DROP TABLE IF EXISTS TB;

DDL操作数据库

DDL(操作数据库，表)

C(Create)

CREATE DATABASE IF NOT EXISTS DB3 CHARACTER SET GBK;创建数据库

R(Retrieve)

查询所有数据库名称

SHOW DATABASES;

查询某个数据库的创建语句

SHOW CREATE DATABASE DB1;

U(update)

修改数据库字符集

ALTER DATABASE DB3 CHARACTER SET UTF8;

D(Delete)

删除数据库

DROP DATABASE (IF EXISTS) 数据库名称；

使用数据库：

查询当前正在使用的数据库名称

SELECT DATABASE();

使用数据库

USE 数据库名称

DML增删改表中数据

1.添加数据

insert into 表名(列名1，列名2，，，列名n) values(值1，值2，，，值n)；除了数字类型其他类型都要用引号引起来

给所有列添加值省略列名

insert into 表名 values(值1，值2，，，值n)；

2.删除数据

delect from 表名[where 条件]；不加条件则全部删除

truncate table stu;删除表在创建一个一模一样的空表

3.修改数据

update 表名 set 列名1 = 值1，列名2 = 值2，列名3 = 值3，，，[where 条件]；不加条件则全部修改

DQL

SELECT * FROM student WHERE age != 20;

SELECT * FROM student WHERE age <> 20; – <>也表示不等于

– 查询大于等于20，小于等于30

SELECT * FROM student WHERE age >=20 && age <=30;

SELECT * FROM student WHERE age >=20 AND age <=30;

SELECT * FROM student WHERE age BETWEEN 20 AND 30;

– 查询22岁，19岁，25岁的信息

SELECT * FROM student WHERE age = 22 OR age = 19 OR age = 25;

SELECT * FROM student WHERE age = 22 || age = 19 || age = 25;

SELECT * FROM student WHERE age IN(22,19,25);

– 查询英语成绩为null

SELECT * FROM student WHERE english = NULL;-- null值不能使用等号判断

SELECT * FROM student WHERE english IS NULL;

– 查询英语成绩不为null

SELECT * FROM STUDENT WHERE ENGLISH IS NOT NULL;

SELECT * FROM STUDENT;

– 查询姓马的有哪些；

SELECT * FROM STUDENT WHERE NAME LIKE ‘马%’;

– 查询姓马的名字两位的

SELECT * FROM student WHERE NAME LIKE ‘马_’;

– 查询第二个字是化的人

SELECT * FROM student WHERE NAME LIKE ‘化%’;

– 查询名字为三个字的人

SELECT * FROM student WHERE NAME LIKE '__’;

– 查询名字中包含马的人

SELECT * FROM student WHERE NAME LIKE ‘%马%’;

DQL2聚合函数

– 按数学成绩排序

SELECT * FROM STUDENT ORDER BY MATH;-- 省略排序方式默认升序(ASC)

SELECT * FROM STUDENT ORDER BY MATH DESC;-- 降序排序

– 按数学成绩排名，若一样则按英语排名

SELECT * FROM STUDENT ORDER BY MATH ASC , ENGLISH DESC;

– 查询有多少人

SELECT COUNT(ENGLISH) FROM STUDENT;-- 聚合函数计算会忽略null值，解决方法如下

SELECT COUNT(IFNULL(english,0)) FROM STUDENT;

SELECT COUNT() FROM student;

– 计算数学成绩最大值,最小值,平均值，求和

SELECT MAX(math) FROM student;

SELECT MIN(math) FROM student;

SELECT AVG(math) FROM student;

SELECT SUM(math) FROM student;

– 分别计算男同学和女同学的平均分,人数

SELECT sex,AVG(math) , COUNT(NAME) FROM student GROUP BY sex;

– 分别计算男同学和女同学的平均分,人数,分数低于70不参与分组

SELECT sex,AVG(math) , COUNT(NAME) FROM student WHERE math > 70 GROUP BY sex;

– 分别计算男同学和女同学的平均分,人数,分数低于70不参与分组,分组之后人数要大于2的

SELECT sex,AVG(math) , COUNT(NAME) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

/

where 和 having的区别

where在分组之前限定，如果不满足则不参与分组，having 在分组后限定不满足不被查询出来

where后不能跟聚合函数，having 可以

*/

– 起个别名

SELECT sex,AVG(math) , COUNT(NAME) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

– 分页查询

– 每页显示3条记录

SELECT * FROM student LIMIT 0,3;-- 第一页

SELECT * FROM student LIMIT 3,3;-- 第二页

– 公式：开始的索引= (当前的页码 - 1)* 每页显示的条数

DQL3多表关系

DROP TABLE IF EXISTS stu;

SHOW TABLES;

– 创建表添加非空约束

CREATE TABLE stu(

id INT,

NAME VARCHAR(20) NOT NULL – 非空

);

DESC stu;

INSERT INTO stu (id,NAME) VALUES(1,‘迪丽热巴’);

SELECT * FROM stu;

INSERT INTO stu (id,NAME) VALUES(2,NULL);

– 删除非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

DELETE FROM stu WHERE id = 2;

– 创建表之后添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

– 添加一列演示唯一约束

ALTER TABLE stu ADD phone_number VARCHAR(20) UNIQUE;

DESC stu;

INSERT INTO stu (id,NAME,phone_number) VALUES(3,‘古力娜扎’,1112);

SELECT * FROM stu;

– 删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

– 删除重新建，演示添加主键

DROP TABLE IF EXISTS stu;

SHOW TABLES;

CREATE TABLE stu(

id INT PRIMARY KEY , – 添加主键

NAME VARCHAR(20)

);

DESC stu;

INSERT INTO stu VALUES(1,‘迪丽热巴’);

INSERT INTO stu VALUES(1,NULL);

SELECT * FROM stu;

– 删除唯一约束

ALTER TABLE stu DROP PRIMARY KEY;

– 创建完表添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

SELECT * FROM stu;

DELETE FROM stu WHERE id=3;

– 主键的自动增长

DROP TABLE IF EXISTS stu;

CREATE TABLE stu(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20)

);

INSERT INTO stu VALUES(NULL,‘aaa’);

SELECT * FROM stu;

– 删除自动增长

ALTER TABLE stu MODIFY id INT;-- 自动增长删除主键依然生效

– 添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

– 外键约束

– 创建表是添加外键语法：

CREATE TABLE 表名(

…

外键列

CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称)

);

CREATE TABLE department(

id INT PRIMARY KEY AUTO_INCREMENT,

dep_name VARCHAR(20),

dep_locationion VARCHAR(20)

);

CREATE TABLE employee(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20),

AGE INT,

dep_id INT,-- 外键对应主表的主键

CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)

);

INSERT INTO department VALUES(NULL,‘研发部’,‘广州’),(NULL,‘销售部’,‘深圳’);

INSERT INTO employee (NAME,age,dep_id) VALUES(‘张三’,20,1),(‘李四’,21,2);

SELECT * FROM employee;

SELECT * FROM department;

– 删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

– 添加外键

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

– 设置级联更新

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;

SELECT * FROM employee;

SELECT * FROM department;

– 添加外键，设置级联更新和级联删除

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY

(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

DELETE FROM department WHERE id=1;

– 一对多关系就是在多的一方添加外键指向另一方的主键

– 多对多关系要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

DLQ4多表查询

隐式内连接：使用WHERE条件清楚数据

例子：

SELECT

T1.NAME,

T1.GENDER,

T2.NAME

FROM

EMP T1,

DEPT T2

WHERE

T1.‘DEPT_ID’ = T2.‘ID’;

显示内连接：

SELECT 字段列表 FROM 表名1 【INNER】 JOIN 表名2 ON 条件

左外连接：

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件；

查询的左表数据以及交集部分