mysql的常用命令

-- 链接数据库
mysql -u root -p
-- 查看数据库
show databases
-- 创建数据库
create database dbname
-- 创建数据库时，定义编码，否则不支持中文，utf8可以支持中文，utf8mb4支持四字节unicode，例如微信名称中的图标，需要这个编码才能支持存储在数据库中。
create database xiaozhoucms default character set utf8mb4 collate utf8mb4_unicode_ci;
-- 切换数据库
use dbname
-- 查看所有表
show tables
-- 修改数据库
alter database dbname character set utf8
-- 删除数据库
drop database dbname

数据类型

1、int：整型
2、double：浮点型，double(6,3)，最长长度是6位，小数点后3位，999.999
3、char：固定长度字符串类型，char(10)，不足10位补足10位，例如身份证。
4、varchar：可变长裤字符串，char(10)，不足10位不会补足，性能低于char，例如个性签名。
5、text：字符串，适用于大文本内容，例如博客内容。
6、date：日期类型 yyyy-MM-dd

常用命令

create table tbname (id int,name varchar(20),sex char(1));
-- 查看表信息
desc tbname;
-- 查询表数据
select * from tbname;
-- 插入数据
insert into tbname values (1，'小明'，0)；
-- 修改数据
update tbname set name = ‘小明’,age = 2 where id = 3;
-- 删除数据
delete from tbname where id = 3;
-- 修改表
alter table tbname add achievement double(5,2)；
-- 修改表
atler table tbname change name username varchar(20)
-- 修改表
alter table tbname drop name
-- 删除表
drop table tbname

SQL的分类

DDL（Data Definition Language）：数据定义语言，用来操作数据库对象：库、表、列等；
create、alter、drop；用于数据库创建阶段。
DML（Data Manipulation Language）：数据操作语言，用来增删改数据库中的数据；
insert、update、delete；用于系统运行阶段
DQL（Data Query Language）：数据查询语言，用来查询数据库中的数据
select

查询语句

先创建表

create table students (
	id int,
	student_name varchar(20),
	sex char(1),
	age int,
	achievement double(5,2),
	team_id int,
	graduation_date date
);

添加数据

INSERT INTO students VALUES (1,'XiaoMing',0,7,100,1,'2020-7-1');
INSERT INTO students VALUES (2,'XiaoHong',1,8,199,1,'2020-7-1');
INSERT INTO students VALUES (3,'XiaoLiang',0,15,60,2,'2020-7-1');
INSERT INTO students VALUES (4,'XiaoMing',0,18,88,2,'2020-7-1');
INSERT INTO students VALUES (5,'XiaoZhang',1,2,110.5,3,'2020-7-1');
INSERT INTO students VALUES (6,'XiaoLi',0,6,30,3,'2020-7-1');
INSERT INTO students VALUES (7,'XiaoLiu',0,3,null,4,'2020-7-1');
INSERT INTO students VALUES (8,'ZhangSan',0,6,50,4,'2020-7-1');
INSERT INTO students VALUES (9,'WangWu',0,6,40,4,'2020-7-1');
INSERT INTO students VALUES (10,'XiaoEr',0,6,40,null,'2020-7-1');

简单的查询

SELECT * FROM students;  
SELECT student,achievement FROM students;
SELECT student_name,achievement,age+18 FROM students;  --不影响数据
SELECT student_name,achievement,age+18 AS 18_years_later FROM students;  --as 设置别名
SELECT student_name,achievement,age,age+18 AS '18年以后' FROM students;

按条件查询

SELECT* FROM students WHERE id = 1;  --where 设置条件
SELECT * FROM students WHERE student_name = 'xiaoming' AND age = 18;
SELECT * FROM students WHERE age > 10 -- <>不等于,>大于，<小于
SELECT * FROM students WHERE age >=3 AND age <= 15;
SELECT * FROM students WHERE age BETWEEN 3 AND 15;
SELECT * FROM students WHERE achievement IS NULL; 
SELECT * FROM students WHERE age = 3 OR age = 18;;
SELECT * FROM students WHERE age IN (3,18,15);
select * from tbname where name like '%x%'; -- 字段包含x的内容。

排序

SELECT * FROM students ORDER BY age;  -- 升序
SELECT * FROM students WHERE achievement >= 60  ORDER BY age; -- 先写条件，在写排序
SELECT * FROM students ORDER BY age ASC; --升序
SELECT * FROM students ORDER BY age DESC; --降序
SELECT * FROM students ORDER BY age, achievement; --按年龄和成绩排序

常用函数

SELECT LOWER(student_name) FROM students; 
SELECT UPPER(student_name) FROM students;
SELECT SUBSTR(student_name,5,2) FROM students;
SELECT student_name FROM students WHERE SUBSTR(student_name,5,1) = "M";
SELECT student_name,LENGTH(student_name) FROM students；
SELECT student_name,IFNULL(achievement,0) FROM students;

聚合函数

SELECT SUM(achievement) FROM students  -- 计算总成绩
SELECT AVG(achievement) FROM students -- 求平均成绩；
SELECT MAX(age) FROM students; -- 求最大年龄
SELECT MIN(age) FROM students; -- 求最小年龄
SELECT COUNT(*) FROM students; -- 查询学生数量
SELECT COUNT(achievement) FROM students; -- 有成绩的学员综合
SELECT DISTINCT student_name FROM students; -- 去除重复数据
SELECT COUNT(DISTINCT student_name) FROM students; 
-- 统计去除重复后的总数。
SELECT sex, COUNT(*) FROM students GROUP BY sex;
-- 查询不同性别的人数
SELECT team_id, SUM(achievement) FROM students
-- 按小组计算总成绩
SELECT team_id, SUM(achievement) FROM students GROUP BY team_id HAVING SUM(achievement) > 100; 
-- 总成绩大于100的小组

limit关键字

SELECT * FROM student LIMIT 5； -- 找到前个学生
SELECT * FROM student LIMIT 2,8;
-- 查询第3个元素到底10个元素（取八个）
SELECT * FROM students ORDER BY achievement DESC LIMIT 5;
-- 学员表中成绩前五的学生。 

完整的select语句

select 
    xxxx
from
    xxxx
where 
    xxxx
group by
    xxxx
having
    xxxx
order by
    xxxx
limit

-- 示例如下
SELECT 
	team_id, SUM(achievement) 
FROM 
	students
WHERE 
	sex = 0 
GROUP BY 
	team_id 
HAVING 
	SUM(achievement) > 100 
ORDER BY 
	SUM(achievement) DESC 
LIMIT 2;

约束与表关系
非空约束
保证字段的值不能为空；
team_name VARCHAR(20) NOT NULL
默认约束
保证字段即使字段不插入数据，也会有一个默认值；
team_name VARCHAR(20) DEFAULT ‘无名队’
主键约束
保证数据不为空，切唯一；
id int primary key auto_increment
外键约束
限制两个表的关系，一个表的外键必须为另一个表的主键，可以为空。
FOREIGN KEY(team_id) REFERENCES teams(id) --定义一对多关系
一对多关系
一个班级对应多个学生；一个学生只能在一个班级；
多对多关系
一个学生选择多个老师的课程，一个老师教多个学生。
一对一关系
并不常见，因为大部分一对一关系的数据都放在一个表中存储；
可能情况：隔离表的与部分数据，为了隐藏或是作为临时数据定期清空。
主键
数据的唯一字段，不能重复。
外键
对应另一个表的主键
对建立一对多关系
创建数据表，查询
创建学生表和教师表

CREATE TABLE teachers(
	id INT PRIMARY KEY AUTO_INCREMENT,
	teacher_name VARCHAR(20)	
)

创建关系表

CREATE TABLE teacher_to_student (id INT PRIMARY KEY AUTO_INCREMENT,
	teacher_id INT,student_id INT,
	FOREIGN KEY(teacher_id) REFERENCES teachers(id),
	FOREIGN KEY(student_id) REFERENCES students(id)
)

插入数据

INSERT INTO teacher_to_student (teacher_id,student_id) VALUES (1,2);

多表查询

SELECT s.student_name,t.team_name FROM students s,teams t WHERE s.team_id = t.id;

--查询学生姓名和对应的班级名称（不推荐）。

1. 内连接：获取两个表交集的所有内容

SELECT s.student_name,t.team_name FROM students s INNER JOIN teams t ON s.team_id = t.id;

2. 左外连接：左表全部 + 右表对应内容

SELECT s.student_name,t.team_name FROM students s LEFT JOIN teams t ON s.team_id = t.id;

3. 右外连接：右表全部 + 左表部分内容

SELECT s.student_name,t.team_name FROM students s RIGHT JOIN teams t ON s.team_id = t.id;

通过关系表查询

-- 查询老师和学生的对应关系
SELECT s.`student_name`,t.`teacher_name` FROM teacher_to_student ts 
	INNER JOIN teachers t ON ts.`teacher_id` = t.`id` 
	INNER JOIN students s ON ts.`student_id` = s.`id`;

子查询

-- 找出成绩高于平均成绩的同学，显示姓名和成绩。
SELECT student_name,achievement FROM students WHERE achievement > (SELECT AVG(achievement) FROM students);

扩展

-- 一个表的一列复制到另一个表中
UPDATE t_user1  SET name = (SELECT username FROM t_user WHERE `password` = t_user1.pass);