MySQL学习笔记

最新推荐文章于 2021-02-27 03:17:08 发布

背包的蜗牛

最新推荐文章于 2021-02-27 03:17:08 发布

阅读量315

点赞数 1

分类专栏：学习文章标签： mysql

本文链接：https://blog.csdn.net/u014114109/article/details/78112123

版权

学习专栏收录该内容

36 篇文章 0 订阅

订阅专栏

SELECT VERSION();   //显示服务器版本

SELECT NOW();       //显示当前时间

SELECT USER();      //显示当前用户

书写规范：关键字与函数名称全部大写，数据库表名称、库名称、字段名称全部小写。

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name; //创建数据库 []为可写可不写

SHOW {DATABASES|SCHEMAS} [LIKE 'pattern'|WHERE expr];    //查看服务器下数据表列表

SHOW CREATE DATABASE databasename;    //显示数据库编码方式

ALTER {DATABASE|SCHEMA} [data_name] [DEFAULT] CHARACTER SET [=] charset_name;     //修改编码方式

ALTER TABLE admin CONVERT TO CHARACTER SET utf8;   //设置整个表中所有字段的编码方式为utf8

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;     //删除数据库

mysql> CREATE TABLE tb1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,//UNSIGNED 表示非负
    -> salary FLOAT(8,2) UNSIGNED  //FLOAT(8,2)一共8位，小数点后有2位  eg：9988.34
    -> );
Query OK, 0 rows affected (0.07 sec)

SHOW TABLES[FROM db_name][LIKE 'pattern'|WHERE expr];   //查看数据表列表

SHOW COLUMNS  FROM tb1;    //描述表tb1

INSERT tb1(username,salary) VALUES('John',4500.69);    //单独给某些字段复制

mysql> CREATE TABLE tb2(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,   //主键约束，自增，主键                          
      -> username VARCHAR(20) NOT NULL                                              
      -> );

UNIQUE KEY

唯一约束

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值NULL

每张表可以存在多个唯一约束

mysql> CREATE TABLE tb3(
    -> id SMALLINT PRIMARY KEY AUTO_INCREMENT,                                      
      -> username VARCHAR(20) UNIQUE KEY                                              
 -> );

DEFAULT 规定默认值

mysql> CREATE TABLE tb4(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'   //ENUM() 1,2,3里选一个值
    -> );

FOREIGN KEYp

外键约束

保持数据一致性，完整性。

实现一对多或一对一的关系。

FOREIGN KEY 物理外键约束如CASCADE,SET NULL,RESTRICT,NO ACTION只能在INNODB数据库引擎中使用，在项目开发中多用逻辑约束。

（主键自动创建索引）

题外话：例如 SHOW CREATE TABLE table_name。展示的是表格形式若：SHOW CREATE TABLE table_name\G，则为另一种形式展现信息（\G后面不能加；）。

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]    //修改表名称 after是规定在哪个字段之后，若不写则在所有字段最下面
ALTER TABLE tbl_name DROP[COLUMN]col_name  //删除列
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;   //举个栗子（添加）
ALTER TABLE users1 ADD(col_name col_definition,...)     //添加多列
ALTER TABLE users1 ADD qq varchar(10),DROP password;   //同时添加和删除，同时删除、添加多个也同理
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);   //添加主键约束 constraint用来给约束起名字
ALTER TABLE users2 ADD UNIQUE (username);             //添加唯一约束
ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces (id);  //添加外键约束
ALTER TABLE users2 ALTER age SET DEFAULT 15;      //添加默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;        //删除默认约束
ALTER TABLE users2 DROP PRIMARY KEY;        //删除主键约束
ALTER TABLE users2 DROP INDEX username;     //删除username的唯一约束  “INDEX”为唯一约束的名字
ALTER TABLE users2 DROP FOREIGN KEY 外键名字;     //删除外键约束  外键名字通过SHOW CREATE TABLE tbl_name里面的constraint位置来查看
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]   //红字部分均可被修改
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;      //“CHANGE”修改，“CHANGE”功能范围大于“MODIFY”

INSERT专题

第一种插入方法当表里的id为自增主键时，插入要用这样两种方法插入

INSERT INTO tbl_name VALUES(NULL,'TOM',25);      //第一个字段为自增，所以设置为NULL让其自增
INSERT INTO tbl_name VALUES(DEFAULT,'TOM',52);   //第一个字段为自增，所以设置为DEFAULT让其自增
INSERT INTO tbl_name VALUES(DEFAULT,'TOM',DEFAULT);  //第三个字段设置表时存在默认值，故可以这样直接插入

INSERT INTO tbname VALUES(DEFAULT,'TOM',10*5-2),(DEFAULT,'JACK',md5('123'));  //插入多个值;md5()为php计算哈希值的函数

第二种插入方法与第一种的区别在于此方法可以使用子查询（SubQuery）

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...

第三种插入方法此方法可直接将查询结果插入到指定数据表

INSERT [INTO] tbl_name [(col_name,...)] SELECT...

UPDATE

eg:UPDATE users SET age=age+1 WHERE id%2=0;   //id为偶数的年龄加1岁

DELETE

DELETE FROM tbl_name [WHERE where_condition]

SELECT的简单介绍

多表链接后具有相同的字段，就这样用

SELECT users.name,users.id FROM users;

SELECT id AS userId,username AS uname FROM users;//起别名

SELECT查询的顺序将影响结果集的顺序;SELECT的别名将影响结果集的别名

GROUP BY分组

SELECT sex FROM users BROUP BY sex; //对性别进行分组  会分成男/女/NULL
SELECT age FROM users GROUP BY 1 HAVING age>20;  //设定了条件后进行分组

SELECT COUNT(age) FROM users GROUP BY age HAVING age=20;   //查找有多少个年龄=20岁的
//HAVING后面必须是：1.一个聚合函数(cout(),min(),max(),sum(),avg(),round(obj,2)[对obj的值四舍五入小数点后两位]等。如：SELECT avg(age) FROM users;2.规定条件的字段必须出现在select后面查找的字段中
//补充：聚合函数count()的用法
SELECT count(*) FROM users;//查询users表中的行数 包括NULL值
SELECT count(sex) FROM users;//查询users表中sex的行数，不包括NULL值，NULL值不算在行数内

ORDER BY排序

SELECT *FROM users ORDER BY id DESC;  //DESC为降序排列，ASC为升序（默认）
SELECT *FROM users ORDER BY age,id DESC //age升序排列同时id降序排列

LIMT 限制

SELECT *FROM users LIMIT 2; //显示数据表的前两行数据
SELECT *FROM users LIMIT 2,2 //显示数据表的第三四行数据，因为第一个2是控制从哪一行开始，第二个2是控制显示几行，又因为数据表是从0开始计算的，所以2代表第三行
select emp_no, salary from salaries where salary = (select salary from salaries group by salary order by salary desc limit 1,1)//order by 后接 limit要注意重复数据的问题，所以最好用这种方式而不是直接order by a limit 1,1

子查询

SELECT AVG(goods_price) FROM tdb_goods;  //计算平均值得出5636.36
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636.36;//条件下计算
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);  //这就是子查询，将两个语句整合在一起。

当子查询返回多个值的时候需要条件判断（any,some,all）写在判断符号后面

如果一张表里面相同字段有相同名称的内容，容易造成查询时间长，所以需要用到外键（实施外键，比物理外键常用）

由于tdb_goods中的货物类型重复较多，且汉字字符串较长，会导致查询变慢，所以将其变成数字来加快查询速度，而这就需要使用另外一张货物类型表的cate_id来进行比对参照，就需要将两张表进行连接

创建tdb_goods_cate后：当需要将查询的结果输入到另一张表时，若得到结果过多，需要使用INSERT...SELECT将查询结果写入数据表，如下

INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
UPDATE tdb_goods INNER JOIN tdb_goods_cate ON goods_cate=cate_name SET goods_cate=cate_id;

实现以上目的可用如下方法进行，仅需两步就可以执行

mysql> CREATE TABLE tdb_goods_brands(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL)
    -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;//通过SELECT实现在建表时直接插入数据
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS q ON g.brand_name=q.brand_name SET g.brand_name=q.brand_id;//两个表中出现同名字段，故用AS起别名后再进行。

内连接只显示符合条件的部分

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;  //好好分析这段代码

左外连接显示左表全部和右表符合条件的

SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;  //好好分析这段代码

连接三张表

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g  INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id INNER JOIN tdb_goods_brands AS q ON g.brand_id=q.brand_id;

无限级分类表设计有时功能往往需求多级分类，但写很多表会很麻烦就需要自身连接想象成两个相同的表，一个父表一个子表

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.parent_id;  //通过这段代码将更好展示出所属关系  （结合课程截图）

今天在做题的时候遇到了个小问题，就是date类型的数据，在插入的时候要加单引号

WHERE后面不能用聚合函数