mysql入门学习

最新推荐文章于 2024-09-16 13:46:32 发布

djxnsiwbfk

最新推荐文章于 2024-09-16 13:46:32 发布

阅读量195

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/Wu_ye123/article/details/102731726

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1，进入对应数据库：mysql -uroot -proot -P3306 -h127.0.0.1

2，

CREATE TABLE tbltest(

username VARCHAR(20),

age TINYINT UNSIGNED,

salary FLOAT(8,2) UNSIGNED

);

----------FLOAT(8,2)表示一共8位中有两位小数

3，

展示表信息    show create table tbltest\G;

4，

SELECT DATABASE();

5，

show columns from tbltest;

INSERT INTO tbl_name (columns_name1,columns_name2)

7，增加默认为空和主键

CREATE TABLE tbltest(

username bigint(20)  UNSIGNED  NOT_NULL AUTO_INCREMENT,

age TINYINT UNSIGNED NULL,

salary FLOAT(8,2) UNSIGNED  NOT_NULL,

PRYMARY KEY (`id`),

);

8，UNIQUE KYE（唯一约束）

保证记录的唯一性、唯一约束的字段可以为空值（NULL）、每张数据表可以存在多个唯一约束

9，defult 默认值

CREATE TABLE tbltest1(
     id bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "主键Id",
     username TINYINT UNSIGNED NOT NULL COMMENT "姓名",
     age TINYINT UNSIGNED NULL COMMENT "年龄",
     salary INT DEFAULT 99999 COMMENT "工资",
     PRIMARY KEY(`id`)
     );

10，外键约束

1>父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

2>数据表的存储引擎只能为InnoDb

3>外键列和参照列必须具有相似的数据类型。其中数字的长度或是否符号位必须相同；而字符的长度则可以不同。

4>外键列和参照列必须创建索引。如果外键列不存在索引的话，Mysql将自动创建索引。

CREATE  TABLE tbltest1( 

id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "主键Id",

pname VARCHAR(20) NOT NULL  COMMENT "省份名",

PRIMARY KEY(`id`)

);

CREATE TABLE tbltest2(

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "主键Id",

username VARCHAR(10) NOT NULL,

pid BIGINT UNSIGNED NOT NULL COMMENT "外键  类型必须相同",

PRIMARY KEY(`id`),

FOREIGN KEY(pid) REFERENCES tbltest1 (id)

);

11,外键约束的参照操作

1>CASCADE:从父表删除或者更新且自动删除或者更新子表中匹配的行

2>SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

3>RESTRICT:拒绝对父表的删除或更新操作

4>NO ACTION:标准SQL的关键字，在Mysql中与RESTRICT相同

INSERT INTO tbltest1  (pname) VALUES('A');
INSERT INTO tbltest1  (pname) VALUES('B');
INSERT INTO tbltest1  (pname) VALUES('C');
INSERT INTO tbltest1  (pname) VALUES('D');

insert into tbltest2 (username,pid) VALUES('zhangsan',3);
insert into tbltest2 (username,pid) VALUES('lisi',4);
insert into tbltest2 (username,pid) VALUES('wangwu',5);//当test1中不存在id为5时

当删除test1中的id为3的记录时，因为外键已经关联test1中id为3的记录，所以不可以进行删除，如果需要删除并且更新，ON DELETE CASCADE

FOREIGN KEY(pid) REFERENCES tbltest1 (id)===> 
            FOREIGN KEY(pid) REFERENCES tbltest1 (id) ON DELETE CASCADE

12,修改数据表结构

ALTER TABLE  user ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; //默认在最后添加
ALTER TABLE  user ADD password VARCHAR(32) NOT NULL AFTER username; //列置于username列后

ALTER TABLE user DROP password，DROP age;

//删除约束
ALTER TABLE user DROP INDEX username;
//删除外键约束
ALTER TABLE user DROP FOREIGN KEY user_name;

13，求平均值

 SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
//goods_price的平均值保留两位小数，值赋予acg_price展示
//ROUND 四舍五入

14，展示大于平均值的商品，并按价格降序排列

SELECT * FROM tdb_goods WHERE goods_price > 5280.73 ORDER BY goods_price DESC;

15,结合13&14进行子查询实现

SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods) ORDER BY goods_price DESC;

16,ANY & SOME & ALL

ANY&SOME 返回多个值时 ‘ > ’会使用其中最小的值

ALL 返回多个值时 '>' 会返回其中最大的值

//降序排列tdb_goods 中 大于任意 销售类型为 '超极本'价格(其中最小的)商品价格
   SELECT goods_id,goods_name,goods_price FROM tdb_goods 
   WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
   ORDER BY goods_price DESC;

//降序排列tdb_goods 中 大于所有 销售类型为 '超极本'价格(其中最大的)商品价格
   SELECT goods_id,goods_name,goods_price FROM tdb_goods 
   WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
   ORDER BY goods_price DESC;

17，IN || NOT IN

18，ERROR 1366 (HY000): Incorrect string value: '\xE8\x8B\xB1\xE5\xAF\xB8...' for column 'goods_name' at row 1

1>   SHOW FULL COLUMNS FROM tdb_goods;

发现字符集(collation)为拉丁文，所以字段cate_name插入值为中文时不支持，现在需要将latin改为utf8

alter table tdb_goods_cates change cate_name cate_name varchar(40) character set utf8 collate utf8_unicode_ci;

19,insert子查询插入

场景：将一张表中某个字段的所有类型的值添加到另外一张表中进行存储，如果数据上百万，需要使用子查询进行自动存储

现将这些goods_cate的值添加到tdb_goods_cates表中

INSERT INTO tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate DESC;

//添加之后又发现了tdb_goods和tdb_goods_cates 中的cate存在相同的值，现在需要将tdb_goods的goods_cate值存为tdb_goods_cates的cate_id，实现多表连接/或者更新

//INNER JOIN
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

语法简化：在创建主表时在括号外面直接添加SELECT brand_name FROM tdb_goods GROUP BY brand_name;

 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;

 
 UPDATE tdb_goods  INNER JOIN tdb_goods_brands ON brand_name = brand_name

  SET brand_name = brand_id;

  -- Column 'brand_name' in field list is ambigous

  -- 正确

  UPDATE tdb_goods AS  g  INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name

  SET g.brand_name = b.brand_id;

疑问：如果删除goods_cates表中的一个记录,那么存放goods的表对应记录会更新或者删除吗？

20，内连接，左外连接，右外连接

内连接(INNER JOIN ... ON ...)

左外连接(LEFT JOIN ... ON ...)

右外连接(RIGHT JOIN ... ON ...)

-- 查询所有商品的详细信息(通过内连接实现)

   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 b ON g.brand_id = b.brand_id\G;

-- 查询所有商品的详细信息(通过左外连接实现)

   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

   LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

   LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

-- 查询所有商品的详细信息(通过右外连接实现)

   SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

   RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

   RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

自身连接

-- 查找所有分类及其父类

  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.type_id;
   
-- 查找所有分类及其子类

  SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS  s ON s.parent_id = p.type_id;

-- 查找所有分类及其子类的数目

  SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

-- 为tdb_goods_types添加child_count字段

  ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;

-- 将刚才查询到的子类数量更新到tdb_goods_types数据表

  UPDATE tdb_goods_types AS t1 INNER JOIN ( SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p 

                                            LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id 
                             
                                            GROUP BY p.type_name 

                                            ORDER BY p.type_id ) AS t2 

  ON  t1.type_id = t2.type_id 

  SET t1.child_count = t2.children_count;


-- 复制编号为12,20的两条记录

  SELECT * FROM tdb_goods WHERE goods_id IN (19,20);


-- INSERT ... SELECT实现复制

  INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);

-- 查找重复记录

  SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

-- 删除重复记录

  DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

多表删除

21, 字符函数

CONCAT , CONCAT_MS , FORMAT , LOWER , UPPER , LEFT , RIGHT

22, 存储引擎特点

锁：共享锁（读锁）在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。

排他锁（写锁）在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁颗粒：表锁：是一种开销最小的锁策略

行锁：是一种开销最大的锁策略

事务：用于保证数据库的完整性

（1）原子性（2）一致性（3）隔离性（4）持久性

外键：保证数据一致性的策略

索引：对数据表中一列或多列的只进行排序的一种结构