MySQL数据库基础操作记录

最新推荐文章于 2024-04-08 10:25:06 发布

qq_36556651

最新推荐文章于 2024-04-08 10:25:06 发布

阅读量347

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_36556651/article/details/91483653

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

库级操作

CREATE DATABASE samp_db CHARACTER SET utf8;#创建名为samp_db 的数据库默认字符集为utf8
DROP DATABASE samp_db;#删除数据库
use samp_db;#进入数据库
SHOW DATABASES;#展示数据库列表
SHOW TABLES;#展示数据库的所有表
DESCRIBE tableName;#查看表结构

创建/删除数据库表

CREATE TABLE score(id INT(10) PRIMARY KEY AUTO_INCREMENT,
stu INT,
NAME VARCHAR(20) NOT NULL DEFAULT '',
score INT)default character set utf8;#建表

CREATE TABLE goods_copy LIKE goods;#创建一张和原来表结构类似的表，不复制表数据

drop table score;#删表

其中id列设置主键自增长，name列不能为空，默认值是‘’，为了应对中文字符问题，在建表时设置默认字符集为utf8。

增删改查

新增

INSERT INTO score(xuehao,name,ke,score) 
VALUES(2011,'wanna','English',93);#添加一条数据

INSERT INTO score(xuehao,name,ke,score) 
VALUES(2011,'wanna','English',93),
(2012,'kobe','English',94);#添加多条数据

INSERT INTO还可实现将另一张表的查询结果插入表中，INSERT时插入参数和查询参数的个数和类型必须一致
INSERT INTO t_detp(name,loc) SELECT name,loc FROM t_loader;#两张表的name和loc类型一致

删除

DELETE FROM score WHERE xuehao=2011;#删除学号为2011的内容
DELETE FROM score WHERE xuehao IN (2011,2012);#删除学号为2011和2012的内容

修改

UPDATE orders SET title='miik',count=2 WHERE id=1;#更新id为1的title、count字段

UPDATE users SET username=(SELECT username FROM user2 WHERE id=2);#从子查询结果中更新users表

UPDATE system SET SYS_LABEL=SYS_NAME;#更新system表设置SYS_LABEL的值为SYS_NAME的值

查询

select * from score;#查询表的所有数据

SELECT id,NAME FROM score;#查询指定字段的数据

SELECT * FROM 
(SELECT cat_id,goods_id,goods_name,shop_price FROM goods ORDER BY shop_orice) 
AS tmp GROUP BY cat_id;#把查询结果当成临时表继续查询，必须取别名

列的增删改

ALTER TABLE boy ADD height TINYINT UNSIGNED NOT NULL DEFAULT 170;#新增height字段，必须为正值且不能为空，默认值是170

ALTER TABLE boy ADD height TINYINT AFTER age;#指定添加到哪个字段后边

ALTER TABLE boy ADD id INT FIRST;#指定添加到开头

ALTER TABLE boy CHANGE height height SMALLINT NOT NULL DEFAULT 180;#修改列

ALTER TABLE age DROP age;#删除列

浮点型

float(M,D)/decimal(M,D)
M是精度（总位数），D是标度（小数点位数） 例如：float(6,2)，存1234.56，总共6位，小数点后面是2位，范围是-9999.99~+9999.99，decimal精度比float高

#建浮点型表格
CREATE TABLE goods(NAME VARCHAR(20),price FLOAT(6,2)) CHARSET utf8;

字符型

char(M)：定长，M代表宽度，即可容纳的字符数，0<=M<=255。
varchar(M)：变长，M代表宽度，即可容纳的字节数，0<=M<=65535。
			可存字符 	实存(i<=M) 		实占空间 		利用率
char 		   M 			i 			   M 		  i/M<=100%
varchar 	   M 			i  		i字符+(1~2)个字节 i/(i+1~2)<100%
         						#1~2个字节用于统计有多少个字符

char类型之所以实占M个宽度，是因为当实存不足M，后面加空格补齐，取出时再把后面空格去掉，所以如果后面有空格将会被清除。varchar即便有空格也会存入。

char和varchar选择原则：
1.空间利用率：四字成语用char(4)，个人简介、微博，140字用varchar(140)。
2.速度：用户名，不需要多少字符，用char，快。

where语句

in(val1,val2....,valn)，括号内的值都成立
SELECT * FROM goods WHERE cat_id IN (2,3,4,5);#查询cat_id是2或3或4或5的商品信息
SELECT * FROM goods WHERE cat_id NOT IN (2,3);#查询cat_id不是2或3的商品信息

between...and...
SELECT * FROM goods WHERE cat_id BETWEEN 20 AND 30;#查询cat_id在20到30之间的商品

模糊搜索(like)
SELECT * FROM goods WHERE good_name LIKE 'apple%';#%是通配任意字符的意思
SELECT * FROM goods WHERE good_name LIKE 'apple_';#_是匹配单个字符，可搜出apple1、apple2、apple3...appleN

SELECT goods_id,goods_name FROM goods 
WHERE goods_id=(SELECT MAX(goods_id) FROM orders);#把内层查询结果作为外层查询条件

group分组

对列进行分组，并配合统计函数
统计函数：max()、min()、sum()、avg()、count()等等。。。
SELECT cat_id,MAX(price) FROM goods GROUP BY cat_id;#以cat_id进行分组查询每组商品最大值

SELECT cat_id,SUM(price*goods_num) AS res FROM goods GROUP BY cat_id;#把列当成变量

group分组与where查询运用
SELECT NAME,MAX(price) FROM items WHERE price>20 GROUP BY NAME;#√正确写法
SELECT NAME,MAX(price) FROM items GROUP BY NAME WHERE price>20;#×错误示范

group分组与having查询运用
SELECT NAME,AVG(price) as avges FROM items GROUP BY having avges>20;#√正确写法

order by…limit

SELECT cat_id,goods_name,shop_price FROM goods WHERE cat_id=3 ORDER BY shop_price DESC;#声明降序排序，升序用ASC

limit[offset],[N]，放在语句最后，起限制条目作用，offset是偏移量，即跳过多少行开始取数据，N是取出的条数。
SELECT goods_id,goods_name,shop_price FROM goods ORDER BY shop_orice LIMIT 3,3;#从第4行开始取，取3条，即4~6行的数据。

SELECT goods_id,goods_name,shop_price FROM goods ORDER BY shop_orice LIMIT 2；#跳过0行，取前两个。

exists子查询

SELECT cat_id,cat_name FROM category WHERE 
EXISTS(SELECT * FROM goods WHERE goods.cat_id=category.cat_id);#先遍历category表，取每条记录的cat_id传给子查询，当子查询有返回结果时，打印出该遍历的记录。

union联合

SELECT user_name,email,msg_content FROM feadback UNION SELECT user_name,email,content FROM COMMENT;#合并查询结果，要求两个语句查询结果的列数要一致，列类型一样，列名可以不一样

两次查询的结果存在完全相同内容的行时会合并（去重复），不想合并可用union all
SELECT * FROM ta UNION ALL SELECT * FROM tb;#若ta和tb中存在相同的一行数据，则都会显示

ANY子查询

=ANY:功能与IN一样
SELECT * FROM t_employee WHERE no=ANY(SELECT no FROM t_dept);

>ANY(>=ANY):比子查询所返回的结果中最小的记录还要大于(大于等于)的记录筛选出来
SELECT NAME,sal FROM t_employee WHERE 
sal>ANY(SELECT sal FROM t_employee WHERE jop='teacher');#结果显示工资不低于职位为teacher的员工

<ANY(<=ANY):比子查询所返回结果中最大的记录还要小于(小于等于)的记录筛选出来
SELECT NAME,sal FROM t_employee WHERE 
sal<ANY(SELECT sal FROM t_employee WHERE jop='teacher');

ALL子查询

>ALL(>=ALL):筛选出比子查询返回结果中最大的记录还要大于(大于等于)的记录
SELECT NAME,sal FROM t_employee 
WHERE sal>ALL(SELECT sal FROM t_employee WHERE jop='teacher');#显示工资高于teacher的员工

<ALL(<=ALL):筛选出比子查询返回结果中最大的记录还要大于(大于等于)的记录
SELECT NAME,sal FROM t_employee 
WHERE sal<ALL(SELECT sal FROM t_employee WHERE jop='teacher');

连接查询语法

#左连接
select * from a_table a left join b_table b on a.a_id = b.b_id;
说明：left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
在这里插入图片描述
#右连接
select * from a_table a right join b_table b on a.a_id = b.b_id;
说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
在这里插入图片描述

#内连接
select * from a_table a inner join b_table b on a.a_id = b.b_id;
说明：查询左右表都有的数据，是左右连接的交集。
在这里插入图片描述

触发器

监视某表的变化(insert\update\delete)，当发生某种变化时，触发某个操作(insert\update\delete)。
触发器创建的4个要素：监视地点(table)、监视事件(insert\update\delete)、触发时间(before\after)、触发事件(insert\update\delete)。
创建触发器的语法：

DELIMITER $
CREATE TRIGGER triggerName 
AFTER/BEFORE 
INSERT/DELETE/UPDATE ON 表名 
BEGIN
 语句1;语句2;
 END$
 DELIMITER ;

例子：

DELIMITER $
CREATE TRIGGER tg 
AFTER 
INSERT ON o 
FOR EACH ROW 
BEGIN
UPDATE g SET num=num-new.much WHERE id=new.gid;
END$
DELIMITER ;

引用新生成的行中列的值，新增行用new表示，引用修改前的行中列的值，用old表示。

事务

指一组操作要么都执行成功，要么都不执行。
ACID（数据库事务正确执行的四个基本要素）
原子性：指在事务中的一组操作，要么都执行，要么都不执行。
一致性：指事务将数据库从一种状态变为下一种一致的状态，事务结束后，数据库的完整性约束没有受到破坏，数据的总额依然匹配。
隔离性：多个事务并发访问时，事务之间是隔离的，一个事务不会影响其它事务的运行效果。
持久性：事务完成以后，该事务对数据库所作的更改会保存在数据库中，不会被回滚。

START TRANSACTION;#开始事务
语句1；语句2；
COMMIT;#提交事务 
OR 
ROLLBACK;#让事务失效

存储过程

类似于函数，将一段代码封装起来，当要执行这段代码的时候，可以通过调用该存储过程来实现，在封装的语句里，可以用if/else、case、while等控制语句，可进行sql编程。

DELIMITER $
CREATE PROCEDURE p(n INT) 
BEGIN 
SELECT * FROM g wehre num>n;
END $
DELIMITER ;

CALL p(10);

索引

查询条件最多的字段适合加索引。
普通索引

CREATE INDEX index_name ON table_name (column_list);
CREATE INDEX index_user ON user(title);#直接创建索引
ALTER TABLE member ADD INDEX index_name(ind);#更改表结构的方式添加索引
CREATE TABLE `table` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `title` CHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `content` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    `time` INT(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX t (TIME)
)#新建表时创建索引
DROP INDEX index_name ON `table`;

主键索引

ALTER TABLE member ADD primiary KEY(id);

唯一索引

ALTER TABLE member ADD UNIQUE email(email);

全文索引

ALTER TABLE member ADD FULLTEXT (intro);

多列索引

ALTER TABLE USER ADD INDEX name_city_age(NAME,city,age);

建立索引的时机：
1.MySQL只对<，<=，=，>，>=，BETWEEN，IN使用索引。
2.而LIKE在以通配符%或者_开头作查询时，不使用索引。
例：

此时就需要对city和age建立索引，
由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
SELECT t.Name  
FROM mytable t LEFT JOIN mytable m ON t.Name=m.username 
WHERE m.age=20 AND m.city='上海';
 
SELECT * FROM mytable WHERE username like'admin%'; -- 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin'; -- 因此，在使用LIKE时应注意以上的区别。