常用基础SQL

– DDL 数据库定义语言

– 创建数据库，字符集utf8，支持中文

CREATE DATABASE school  DEFAULT CHARSET "UTF8";

– USE 切换到某个数据库

use school;

– 创建数据表

CREATE TABLE `wh_student02` (
    `id` int PRIMARY KEY AUTO_INCREMENT COMMENT "主键 自增",
		`name` VARCHAR(30) NOT NULL COMMENT "学生姓名",
	  `sex` ENUM("男","女") NOT NULL DEFAULT "女" COMMENT "学生性别" , 
		`age` INT NOT NULL COMMENT "学生年龄"
)ENGINE="INNODB";

– 查看所有库

show databases;

– 查看数据库的创建信息

show CREATE DATABASE school;

– 查看当前所在的库

SELECT DATABASE();

– 查看当前用户

SELECT USER();
USE school;

– 查看数据库中有哪些表

SHOW TABLES;

– 删除数据库

DROP DATABASE `school`;

– 数据表的 DDL操作
– 修改表名称

RENAME TABLE wh_student02 TO wh_cloud2402;
ALTER TABLE wh_cloud2402 RENAME TO wh_student02;

– 添加字段
– 默认添加在最后一列

ALTER TABLE wh_student02 ADD `ai` VARCHAR(20);

– 添加在第一列

ALTER TABLE wh_student02 ADD `aihao` VARCHAR(20) FIRST; 

– 添加到某一列之后

ALTER TABLE wh_student02 ADD `ah` VARCHAR(20) AFTER `name`;

– 删除字段

ALTER TABLE wh_student02 drop aihao;

– 修改字段数据类型和约束

ALTER TABLE wh_student02 MODIFY ah INT NOT NULL;

– 修改字段名字、类型和约束

ALTER TABLE wh_student02 CHANGE ah aihao VARCHAR(20);

– 修改字段数据类型和约束，并将其放到某一列

ALTER TABLE wh_student02 MODIFY aihao VARCHAR(20) NOT NUll AFTER age;

– 查看表结构

DESC wh_student02;

– 删除表

DROP TABLE wh_student02;

– 复制表

CREATE TABLE `wh`(SELECT * FROM wh_student02);

-- DML 数据操作语言
-- INSERT、DELETE、UPDATE
CREATE TABLE student(
id int NOT NULL PRIMARY KEY auto_increment,
`name` VARCHAR(20),
age INT
);
-- 新增一条数据
insert into student values(1,"tom",22);
insert into student(id,`name`,age) values(1,"tom",22);
-- 新增多条数据
insert into student values(1,"tom",22),(2,"xiaohua",19),(3,"xiaozao",33);
-- 在某个字段新增数据
INSERT INTO student(`name`) VALUES ("小李"),("小王")

-- 更新一条记录中的一个字段
SELECT * FROM student WHERE id = 5;
UPDATE student set age="20" where id = 5;

-- 更新一条记录中的多个字段
SELECT * FROM student WHERE id = 5;
UPDATE student set age="20",name="xiaowei" where id = 5;

-- 删除数据
DELETE FROM student where id = 3;
SELECT * from student;

-- 清空数据表中的所有数据
DELETE FROM student;
TRUNCATE student; 
select DATABASE();
DELETE FROM t1;
truncate t1;

-- 简单查询
	SELECT * from employee5;
	SELECT `name`,salary,office FROM employee5;
	
	-- 查看有多少办公室 去重
	SELECT DISTINCT office FROM employee5;
	
	-- 公司14薪
	SELECT `name`,salary,salary*14 FROM employee5;
	SELECT `name`,salary,salary*14 as Annual_salary FROM employee5;
	SELECT name, salary, salary*14 Annual_salary FROM employee5;

SELECT CONCAT(`name`,'-',`salary`) as Annual_salary FROM employee5;
SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM employee5;

	-- 条件查询，查询薪资为2200
	SELECT * FROM employee5 where salary = 2200;
	
	-- 在501办公室，薪资大于5000的 = > < >= <=
	SELECT * FROM employee5 WHERE salary >= 5000 and office = 501;
	
	-- 薪资为5000 6000 8000的有多少 [and 且] [or 或] [IN 集合]
	SELECT * FROM employee5 WHERE salary = 5000 or salary = 6000 or salary = 8000;
	SELECT * FROM employee5 WHERE salary IN (5000,6000,8000);
	
	-- 薪资大于5000，小于6000
	SELECT * FROM employee5 WHERE salary > 5000 and salary < 6000;
	
	-- 查询薪资5000-15000之间
	SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
	
	-- not 非 ， 小于5000 或者 大于15000
    SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
	
	
	SELECT * from employee5 WHERE job_description is not null;
	
	-- asc 升序排列， desc降序排列
	SELECT `name`,`salary` FROM employee5 ORDER BY salary asc;
	SELECT `name`,`salary` FROM employee5 ORDER BY salary DESC;
	
	-- 分页查询 2,5 从第2条数据之后开始，显示5条记录
	SELECT * from employee5 limit 3;
	SELECT * FROM employee5 LIMIT 2,5;
	
	SELECT * FROM employee5 ORDER BY salary;
	SELECT * FROM employee5 ORDER BY sex;
	
	SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
	SELECT * from employee5 ORDER BY hire_date DESC;

-- 分组查询

SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
select dep_id FROM employee5 GROUP BY dep_id;
SELECT `name` FROM employee5;
-- 查看2020年入职的员工
SELECT * from employee5 where hire_date like "2020%"; -- 模糊匹配
-- 在hire_date字段内,从左边数4个字符，这四个字符=2020；
SELECT * from employee5 where LEFT(hire_date,4) = 2020;
SELECT * from employee5 where RIGHT(hire_date,5) = "02-02";
SELECT * from employee5 WHERE hire_date REGEXP "2020.";
SELECT * FROM employee5 WHERE hire_date > "2019-12-31" and hire_date < "2021-01-01";
SELECT * FROM employee5 WHERE hire_date BETWEEN "2019-12-31" and  "2021-01-01";
-- 统计表中有多少行数据
SELECT count(1) FROM employee5;
SELECT avg(salary) FROM employee5;