数据库操作
数据库服务
#启动数据库
mysql.server start
Starting MySQL
.. SUCCESS!
#登录数据库
mysql -u root -p
Enter password:
#退出数据库
exit
Bye
#关闭数据库
mysql.server stop
Shutting down MySQL
.. SUCCESS!查看数据库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)创建数据库
create database 数据库名;
mysql> create database test;
Query OK, 1 row affected (0.00 sec)删除数据库
drop database 数据库名;
mysql> create database delsql;
Query OK, 1 row affected (0.00 sec)
mysql> drop database delsql;
Query OK, 0 rows affected (0.01 sec)使用数据库
use 数据库名;
mysql> use test;
Database changed查看当前使用的数据库
select database();
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)表操作
查看表
show tables;
mysql> show tables;
Empty set (0.00 sec)创建表
create table 表名
mysql> create table user(id int,name varchar(20),age int,birthday datetime);
Query OK, 0 rows affected (0.02 sec)删除表
drop table 表名
mysql> create table test(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)删除表中数据
truncate table 表名
删除表中数据和索引，表还在
mysql> truncate table b;增加数据
insert into 表名 values();
mysql> insert into user values(1,"xyq",30,"1990-06-19");
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(2,"lqm",27,"1993-06-09");
Query OK, 1 row affected (0.01 sec)删除数据
delete
mysql> delete from user where id=2;
Query OK, 1 row affected (0.01 sec)修改数据
update
mysql> update user set age=28 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0查询数据
查询全部数据
select * from 表名;
mysql> select * from user;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)查询某列数据
select 列名 from 表名;
mysql> select name from user;
+------+
| name |
+------+
| xyq |
| lqm |
+------+
2 rows in set (0.00 sec)
mysql> select name,age from user;
+------+------+
| name | age |
+------+------+
| xyq | 30 |
| lqm | 27 |
+------+------+
2 rows in set (0.00 sec)使用别名查询
select 列名 别名 from 表名；
mysql> select name 姓名,age 年龄 from user;
+--------+--------+
| 姓名 | 年龄 |
+--------+--------+
| xyq | 30 |
| lqm | 27 |
+--------+--------+
2 rows in set (0.00 sec)条件查询
select * from 表名 where 条件;
mysql> select * from user where id = 1;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
+------+------+------+---------------------+
1 row in set (0.00 sec)and
mysql> select * from user where id=1 and age=30;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
+------+------+------+---------------------+
1 row in set (0.00 sec)or
mysql> select * from user where id=1 or age=27;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)between
mysql> select * from user where age between 26 and 35;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)in
mysql> select * from user where age in(27,30);
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)模糊查询
%
mysql> select * from user where age like '3%';
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
+------+------+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from user where birthday like '%06%';
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)排序
order by asc/desc 默认asc顺序
mysql> select * from user order by age;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from user order by id desc;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 2 | lqm | 27 | 1993-06-09 00:00:00 |
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)s限制条数
limit
mysql> select * from user limit 1;
+------+------+------+---------------------+
| id | name | age | birthday |
+------+------+------+---------------------+
| 1 | xyq | 30 | 1990-06-19 00:00:00 |
+------+------+------+---------------------+
1 row in set (0.00 sec)不重复
distinct
mysql> select distinct name from user;
+------+
| name |
+------+
| xyq |
| lqm |
+------+
2 rows in set (0.00 sec)统计
count
mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)平均数
avg
mysql> select avg(age) from user;
+----------+
| avg(age) |
+----------+
| 28.5000 |
+----------+
1 row in set (0.00 sec)最大值
max
mysql> select max(age) from user;
+----------+
| max(age) |
+----------+
| 30 |
+----------+
1 row in set (0.00 sec)最小值
min
mysql> select min(age) from user;
+----------+
| min(age) |
+----------+
| 27 |
+----------+
1 row in set (0.01 sec)求和
sum
mysql> select sum(age) from user;
+----------+
| sum(age) |
+----------+
| 57 |
+----------+
1 row in set (0.00 sec)分组
group by
mysql> select avg(age),id from user group by id;
+----------+------+
| avg(age) | id |
+----------+------+
| 30.0000 | 1 |
| 28.0000 | 2 |
+----------+------+
2 rows in set (0.00 sec)联合
union
联合查询排重
mysql> select id from student union select stuid from grade;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 8 |
+----+
6 rows in set (0.00 sec)union all
联合查询不排重
mysql> select id from student union all select stuid from grade;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
| 8 |
+----+
13 rows in set (0.00 sec)查询表信息
查看建表语句
show
mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
`birthday` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)查看表的数据类型
desc
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)查询时间
查询当前日期
curdate()
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-07-14 |
+------------+
1 row in set (0.00 sec)查询当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 17:44:40 |
+-----------+查询当前日期和时间
now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-07-14 17:44:23 |
+---------------------+
1 row in set (0.00 sec)外键
表B的某一列关联表A的某一列，且B这列数据的所有值，必须都在表A的关联中出现,优点是数据的一致性
建表
# a表的id是b表uid的外键
mysql> create table a(
-> id int,
-> primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> create table b(
-> uid int,
-> primary key(uid),
-> CONSTRAINT FK_ID FOREIGN KEY(uid) REFERENCES a(id));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into a values (2);
Query OK, 1 row affected (0.00 sec)
insert into b values(1);
Query OK, 1 row affected (0.00 sec)
# 外键约束，b表无法添加a表中没有的数据
mysql> insert into b values(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `FK_ID` FOREIGN KEY (`uid`) REFERENCES `a` (`id`))
mysql> insert into b values(1);
# 外键约束，a表无法删除b表中已经存在的数据
mysql> delete from b wher id=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id=1' at line 1索引
索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。
Mysql索引主要有两种结构：B+Tree索引和Hash索引。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。
explain
mysql> explain select * from b;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | b | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)实战
建表
创建学生表字段id name sex date，id为主键,自增
主键特点：不重复，搜索速度快
mysql> create table student(
-> id int not null auto_increment,
-> name varchar(20) not null,
-> sex varchar(2),
-> date date,
-> primary key(id)
-> )engine=innodb character set utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
insert into student values(1,"张三","男","2020-07-15");
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+------+------------+
| id | name | sex | date |
+----+--------+------+------------+
| 1 | 张三 | 男 | 2020-07-15 |
| 2 | 李四 | 女 | 2020-07-15 |
| 3 | 王五 | 男 | 2020-07-10 |
| 4 | 赵六 | 男 | 2020-06-12 |
| 5 | 孙七 | 女 | 2020-05-12 |
+----+--------+------+------------+
5 rows in set (0.00 sec)创建成绩表id stuid class grade,id为主键
mysql> create table grade(
-> id int not null auto_increment,
-> stuid int not null,
-> class varchar(20),
-> grade int,
-> primary key(id)
-> )engine=innodb character set utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into grade (stuid,class,grade) values(1,"计算机",100);
Query OK, 1 row affected (0.00 sec)
mysql> select * from grade;
+----+-------+-----------+-------+
| id | stuid | class | grade |
+----+-------+-----------+-------+
| 1 | 1 | 计算机 | 100 |
| 2 | 1 | 吉他 | 90 |
| 3 | 1 | 美术 | 70 |
| 4 | 2 | 计算机 | 70 |
| 5 | 2 | 吉他 | 90 |
| 6 | 2 | 美术 | 80 |
| 7 | 3 | 吉他 | 50 |
+----+-------+-----------+-------+
7 rows in set (0.00 sec)查询
查询学号1的学生总成绩
mysql> select sum(grade) from grade where stuid=1;
+------------+
| sum(grade) |
+------------+
| 260 |
+------------+
1 row in set (0.00 sec)查询所有学生总成绩
mysql> select stuid,sum(grade) from grade group by stuid;
+-------+------------+
| stuid | sum(grade) |
+-------+------------+
| 1 | 260 |
| 2 | 240 |
| 3 | 50 |
+-------+------------+
3 rows in set (0.00 sec)查询所有学生平均成绩
mysql> select stuid,avg(grade) from grade group by stuid;
+-------+------------+
| stuid | avg(grade) |
+-------+------------+
| 1 | 86.6667 |
| 2 | 80.0000 |
| 3 | 50.0000 |
+-------+------------+
3 rows in set (0.00 sec)查询学生姓名和总成绩
内连接(效率相对外连接低一些)
student表中id在grade表中stuid中出现，数据会显示在表中
mysql> select student.name,sum(grade) from student,grade where student.id=grade.stuid group by grade.stuid;
+--------+------------+
| name | sum(grade) |
+--------+------------+
| 张三 | 260 |
| 李四 | 240 |
| 王五 | 50 |
+--------+------------+
3 rows in set (0.00 sec)
mysql> select student.name,sum(grade) from student inner join grade on student.id=grade.stuid group by grade.stuid;
+--------+------------+
| name | sum(grade) |
+--------+------------+
| 张三 | 260 |
| 李四 | 240 |
| 王五 | 50 |
+--------+------------+
3 rows in set (0.00 sec)左连接
student 表的id在grade表的stuid出现的话显示相关的grade表数据，若不出现在grade表，则相关b表的数据行显示null
mysql> select student.name,sum(grade) from student left join grade on student.id=grade.stuid group by student.id;
+--------+------------+
| name | sum(grade) |
+--------+------------+
| 张三 | 260 |
| 李四 | 240 |
| 王五 | 50 |
| 赵六 | NULL |
| 孙七 | NULL |
+--------+------------+
5 rows in set (0.00 sec)右连接
grade表的stuid在student表的id出现的话显示相关的student表数据，若不出现在student表，则相关student表的 数据行显示null
mysql> select student.name,sum(grade) from student right join grade on student.id=grade.stuid group by grade.stuid;
+--------+------------+
| name | sum(grade) |
+--------+------------+
| 张三 | 260 |
| 李四 | 240 |
| 王五 | 50 |
| NULL | 50 |
+--------+------------+
4 rows in set (0.00 sec)查询总成绩最高的学生姓名和成绩
mysql> select student.name,sum(grade) from student right join grade on student.id=grade.stuid group by grade.stuid order by sum(grade) desc limit 1;
+--------+------------+
| name | sum(grade) |
+--------+------------+
| 张三 | 260 |
+--------+------------+
1 row in set (0.00 sec)查询成绩第二高的学生成绩和姓名
mysql> select student.name,sum(grade) from student right join grade on student.id=grade.stuid group by grade.stuid order by sum(grade) desc limit 1,1; +--------+------------+
| name | sum(grade) |
+--------+------------+
| 李四 | 240 |
+--------+------------+
1 row in set (0.00 sec)建表
一个班级有4位学生，考3门试，根据以下数据，写出SQL涉及3门学科总分最高的学生及总分成绩，并按总分降序排序
mysql> create table class(
-> name varchar(2),
-> subject varchar(20),
-> grade int)engine=innodb character set utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into class values("D","数学",88);
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+------+---------+-------+
| name | subject | grade |
+------+---------+-------+
| A | 语文 | 80 |
| A | 英语 | 90 |
| A | 数学 | 70 |
| B | 语文 | 70 |
| B | 英语 | 100 |
| B | 数学 | 75 |
| C | 语文 | 78 |
| C | 英语 | 80 |
| C | 数学 | 95 |
| D | 语文 | 81 |
| D | 英语 | 85 |
| D | 数学 | 88 |
+------+---------+-------+
12 rows in set (0.00 sec)查询
mysql> select name,sum(grade) from class group by name order by sum(grade) desc;
+------+------------+
| name | sum(grade) |
+------+------------+
| D | 254 |
| C | 253 |
| B | 245 |
| A | 240 |
+------+------------+
4 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
