MySQL数据库入门
-
-
介绍&安装
https://blog.csdn.net/weixx3/article/details/80782479 安装教程
https://www.osyunwei.com/archives/2014.html 修改密码 会遇到问题因为mysql版本所以下面的链接
https://www.cnblogs.com/wangbaobao/p/7087032.html 修改密码 修改密码可能出现密码策略问题
https://blog.csdn.net/hello_world_qwp/article/details/79551789 修改密码策略
https://www.cnblogs.com/cpl9412290130/p/9583868.html 解决权限问题
检查mysql服务状态:systemctl status mysql.service
我的mysql密码是root
sudo mysql -u root -p 登录到mysql
-
DML
select查询语句,左外链接,右外连接;insert 插入语句,;update修改语句,;delete删除语句,。
-
DDL
create,;drop,;truncate,;rename,;alter,; -
存储引擎
-
字段类型
权限管理
-
权限管理开场
-
MySQL权限验证
-
常见场景举例
-
权限安全
常用系统命令
-
常用系统命令(上)
-
常用系统命令(下)
作业
图书表
id book_id author_id book_name pages press
奖项表
id book_id author_id cup_type cup_time
作者表
id author_id author_name content
一、设计表,写出建表语句(我给出的字段内容、名称仅供参考,各位同学可以按照自己的设计建表)
crete database data0709;
use data0709;
create table d_book(
id int(10) NOT NULL AUTO_INCREMENT COMMENT'id',
book_id varchar(20) NOT NULL COMMENT 'book id',
author_id varchar(20) NOT NULL COMMENT 'author id',
book_name varchar(20) NOT NULL COMMENT 'book name',
book_pages varchar(20) NOT NULL COMMENT 'book page',
book_press varchar(20) NOT NULL COMMENT 'book press',
primary key(id));
create table d_author(
id int(10) NOT NULL AUTO_INCREMENT COMMENT'id',
author_id varchar(20) NOT NULL COMMENT 'author id',
author_name varchar(20) NOT NULL COMMENT 'author name',
content varchar(20) COMMENT 'content',
primary key(id));
create table d_prize(
id int(10) NOT NULL AUTO_INCREMENT COMMENT'id',
book_id varchar(20) NOT NULL COMMENT 'book id',
prize_type varchar(20) NOT NULL COMMENT 'prize type',
prize_time Date NOT NULL COMMENT 'prize time',
author_id varchar(20) NOT NULL COMMENT 'author id',
primary key(id));
二、设计索引,写出创建索引的语句
首先分析这三个的关系,一个作者可以写多本书,某作者以某书获得某个奖,一个人通过一本书可以获得多个奖项。
ALTER TABLE d_book ADD UNIQUE uk_book_author (author_id,book_id);
ALTER TABLE d_prize ADD UNIQUE uk_book_author_prize (author_id,book_id,prize_type);
三、完成以下SQL
1. 查询姓王的作者有多少
先插入一些数据哈~insert into d_author(author_id,author_name) values ('2','海波');
插入时候会遇到不能插入中文,解决办法:alter table d_author change author_name author_name varchar(255) character set utf8;
然后再查找姓王的作=作者有多少:
select count(*) from d_author where author_name like '王%';
2. 查询页数最多的前5名作者姓名和书名
select d_book.book_name,d_author.author_name from d_book join d_author on d_book.author_id=d_author.author_id order by book_pages desc limit 5;
3. 查询获奖最多的作者姓名,获奖时间
select d_prize.prize_time,d_author.author_name from d_author join d_prize on d_prize.author_id=d_author.author_id group by d_prize.author_id order by count(author_name) desc limit 1;
4. 查询获奖作者总人数
select count(distinct author_id ) from d_prize;
5. 查询最近获奖的一本书名和出版社
select d_book.book_press,d_book.book_name from d_book join d_prize on d_prize.book_id=d_book.book_id order by d_prize.prize_time desc limit 1;
6. 查询同时获得过金奖、银奖的作者姓名
SELECT d_author.author_name FROM d_author INNER JOIN d_prize ON d_author.author_id = d_prize.author_id WHERE prize_type = "金奖" OR prize_type = "银奖" GROUP
BY d_prize.author_id HAVING COUNT( * ) = 2;
7. 查询获得过金奖的图书有多少本,银奖的有多少本
SELECT prize_type, COUNT(prize_type) from d_prize where prize_type='金奖'or prize_type='银奖' GROUP BY prize_type;
8. 查询最近一年内获过奖的作者姓名
select distinct d_author.author_name from d_author join d_prize on d_author.author_id = d_prize.author_id where prize_time>DATE_SUB( CURDATE( ), INTERVAL 1 YEAR );
9. 查询每位作者各自出版的图书名
select author_name,group_concat(book_name) from d_author join d_book on d_book.author_id=d_author.author_id group by author_name;
四、
1.如何查看表的结构信息?
show create table 表名;
2.联合索引中的字段顺序应该如何设计?
通常就按照where条件中字段的顺序建立 ,这样根据最左匹配原则才可以生效。
https://www.cnblogs.com/goody9807/p/7396195.html
3.int(10)和varchar(10)两个字段的(10)有什么区别?
int(10)的10表示显示的数据的长度,不是存储数据的大小;chart(10)和varchar(10)的10表示存储数据的大小,即表示存储多少个字符。
4.以下查询如何创建索引能够实现覆盖索引优化?
select invalid_time_flag from pushtoken_android_62 where uid = ’AC54E24E-FB73-3981-C4BC-CED8D69407F8’and pid = ‘10010’
select count(*) from pushtoken_android_62 where uid = ’AC54E24E-FB73-3981-C4BC-CED8D69407F8’and pid = ‘10010’
提示:调整索引时请给出依据,要有explain的说明前后对比。
创建表并加数据:
create table ushtoken_android_62(
id int(10) NOT NULL AUTO_INCREMENT COMMENT'id',
uid varchar(20) NOT NULL COMMENT 'uid',
pid varchar(20) NOT NULL COMMENT 'pid',
tokenid varchar(20) NOT NULL COMMENT 'token id',
primary key(id));
insert into ushtoken_android_62 (uid,pid,tokenid) values ('AC54E24E-F8','10010','hikhkjhjh');
证明最左匹配原则的过程:
没有索引情况:
explain select tokenid from ushtoken_android_62 where uid = 'AC54E24E-F8' ;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ushtoken_android_62 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
添加查询顺序的索引:
ALTER TABLE ushtoken_android_62 ADD UNIQUE uk_uid_pid (uid,pid);
explain select tokenid from ushtoken_android_62 where uid = 'AC54E24E-F8' and pid = '10010';
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | ushtoken_android_62 | NULL | const | uk_uid_pid | uk_uid_pid | 44 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------+
删除索引,换字段顺序
ALTER TABLE ushtoken_android_62 drop index uk_uid_pid
ALTER TABLE ushtoken_android_62 ADD UNIQUE uk_pid_uid (pid,uid);
explain select tokenid from ushtoken_android_62 where uid = 'AC54E24E-F8';
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ushtoken_android_62 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
explain select tokenid from ushtoken_android_62 where uid = 'AC54E24E-F8' and pid = '10010';
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | ushtoken_android_62 | NULL | const | uk_pid_uid | uk_pid_uid | 44 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------+
explain select count(*) tokenid from ushtoken_android_62 where uid = 'AC54E24E-F8' and pid = '10010';
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | ushtoken_android_62 | NULL | const | uk_pid_uid | uk_pid_uid | 44 | const,const | 1 | 100.00 | Using index |
+----+-------------+---------------------+------------+-------+---------------+------------+---------+-------------+------+----------+------------