-- 链接数据库
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);