创建database
# 创建数据库 指定数据库编码及校对，一般选择utf8 utf8_genenral_ci 避免中文乱码
create database db_name default character set utf8 collate utf8_general_ci;utf8_general_ci;
DROP DATABASE db_name; -- 删除数据库
SELECT DATABASE(); -- 查看当前使用数据库
USE db_name; -- 切换数据库
创建table
# 其中id 为字段， INT(11) 指定了id为int类型数值，长度为11
# PRIMARY KEY 指定为主键，主键不能为空
# AUTO_INCREMENT 指定为自增
CREATE TABLE Student(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age TINYINT(3) UNSIGNED,
gender ENUM("男", "女", "保密") DEFAULT "保密"
)
# 查看表描述信息
DESC Student;
修改表结构
ALTER TABLE Student ADD brithday DATETIME; -- 添加字段
ALTER TABLE Student MODIFY brithday DATE; -- 修改字段 不重命名字段
ALTER TABLE Student change brithday birth DATE; -- 重命名字段
添加表数据
-- 插入一行数据 当全部字段都插入时不需要再student后面跟表字段
INSERT INTO Student VALUES(1, "jack", "12", "男", "1996-12-11");
-- 插入数据到指定列, 当不插入全部字段时，需要在student表后跟上字段名
INSERT INTO Student(NAME, age, birth) VALUES("lucy", "10", "2010-1-1");
-- 插入多行
INSERT INTO Student(NAME, age, birth) VALUES("laowang", 11, "2009-1-1"), ("22", 12, "1992-2-22");
修改表数据
# 根据条件修改字段值
UPDATE Student SET birth="2000-2-22" WHERE NAME="two_two";
# 根据条件删除数据
DELETE FROM Student WHERE id="1";
# 将age大于10得gender设置为男
UPDATE Student SET gender="男" WHERE age>10;
# 将age小于等于10得gender设置为女
UPDATE Student SET gender="女" WHERE age<=10;
# 将name全部设置为jack
UPDATE Student SET NAME="jack";
#将gender为女得name设置为rose
UPDATE Student SET NAME="rose" WHERE gender="女";
# 删除name为jack和age为11得行
DELETE FROM Student WHERE NAME="jack" AND age=11;
查询语句
SELECT * FROM Student; -- 查询整个数据表
SELECT NAME, age, gender FROM Student; -- 查询指定字段
SELECT DISTINCT NAME, age, gender FROM Student; -- 查询指定字段并去重
# > < != <> = or and like ....
SELECT * FROM Student WHERE age<=10; -- 查询age小于等于10得数据
SELECT * FROM Student WHERE gender!="男"; -- 查询gender不等于男得数据
SELECT * FROM Student WHERE gender!="男" OR age>10; # 模糊查询like % 表示任意多个字符， _ 表示一个字符
SELECT * FROM Student WHERE NAME LIKE "r%"; # 1.查询名字以r开始的学生信息
SELECT * FROM Student WHERE NAME LIKE "%a%"; # 2.查询名字含有a的学生信息
SELECT * FROM Student WHERE NAME LIKE "__"; # 3.查询名字仅有2个字符的学生信息
SELECT * FROM Student WHERE NAME LIKE "__%"; # 4.查询名字至少有2个字符的学生信息
# 范围查询： in 表示再一个非连续范围， between and 表示再一个连续范围
# 1.查询id是30或者31或者32的学生信息
SELECT * FROM student WHERE id BETWEEN 30 AND 32;
# 2.查询年龄为18或者20的学生信息
SELECT * FROM student WHERE age IN(18, 12);
# 3.查询年龄不是18,20的学生信息
SELECT * FROM student WHERE age NOT IN(18, 12);
# 4.查询id是2至4的学生信息
SELECT * FROM student WHERE (age NOT BETWEEN 18 AND 20);
# 判断是否为空 is null / is not null
# 1.查询没有填写gender的学生
SELECT * FROM student WHERE birth IS NULL;
python交互
import pymysql
# 连接数据库 此时需要连接的主机、端口、数据库名、用户、密码及编码
# 编码一般选择utf8 避免乱码
# 如果是本地数据库可以localhost 或 127.0.0.1 端口默认为3306
con = pymysql.connect(host='localhost', port=3306, database='students', user='root', password='root', charset='utf8')
# 创建游标
cs = con.cursor()
# 可以再execute方法中写入原生sql并执行
cs.execute('show databases')
# 需要注意的是，使用sql语句查询之后的结果，需要使用fetch方法来读取数据
databases = cs.fetchall()
用户操作
-- 全局级别
create user 'xxx'@'localhost' identified by '111'; # 创建用户
grant all on *.* to 'xxx'@'localhost'; # 给用户添加所有数据库所有权限
select * from mysql.user where user='xxx'; # 查看用户信息
drop user 'xxx'@'localhost'; # 删除用户
-- 数据库级别
create user 'xxx'@'%' identified by '666';
grant select, update on my_train.* to 'xxx'@'%';
select * from mysql.db where user='xxx';
-- 表级别
create user 'xxx'@'%' identified by '666';
grant select, update on my_train.students to 'xxx'@'%';
select * from mysql.tables_priv where user='xxx';
主键&外键
当我们创建表的时候需要对另一表进行关联，则可以同过创建外键与另一表的主键进行关联，即可对两张表格进行关联并起约束作用。
主键： 非空、唯一、索引
外键： 可以为空但得和主键对应、不唯一、索引
- 通过创建表的方式创建主键
create table students (
s_id int primary key,
name varchar(8)
);
- 通过创建表的方式创建外键
create table s_info (
s_id int primary key ,
age int,
sex enum('男', '女', '保密'),
constraint fk_id foreign key(s_id) references students(s_id)
);
- 直接删除主表
外键所在的表格为从表，主键所在的表为主表，如果要删除主表，需要先保证其主键没有对应的外键，即需要先删除从表，或者外键，否则会报错如下。很好的保证了表的完整性。
11:49:10 drop table students Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails 0.157 sec
# 上面提到了可以在创建表的时候直接创建主键，也可以通过后面修改表结构添加主键
alter table tb_name modify f_name id primary key;
# 删除主键
alter table tb_name drop primary key;
# 创建外键
constraint fk_name foreign key(field_name) references t_name(f_nmae);
# 查看索引
show index from t_name;
索引及优化
使用索引可以很好的增加查询表的效率，索引类似于一本词典的目录，可以根据目录快速定位到需要查询的数据，但是索引也不宜过多，否则会影响数据写入的效率，但是索引的使用有很多的注意事项，稍不注意索引就会失效，导致全表扫描，降低读取效率，最好在使用改sql之前使用explain进行分析。
- 创建索引
-- 创建的索引名称的规范写法： idx_ + fieldname1 + fieldname2
create index idx_fname on t_name(field1, field2);
-- 删除索引
drop index idx_name on t_name;
- explain的使用
1、type
all < index < range < ref < eq_ref < const < system < null
从左到右，性能从差到好，一般我们只用关注all 到eq_ref
all：全表扫面
index：索引全扫描
range：检索指定范围行
ref：非唯一性索引扫描
eq_ref： 唯一索引扫描
更多详细可参考：
https://blog.csdn.net/why15732625998/article/details/80388236
- 索引优化
-- 创建复合索引
create index idx_asw on s_info(s_id, age, weight);
-- 查看索引
mysql> show index from s_info;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| s_info | 1 | idx_asw | 1 | s_id | A | NULL | NULL | NULL | | BTREE | | | YES |
| s_info | 1 | idx_asw | 2 | age | A | NULL | NULL | NULL | YES | BTREE | | | YES |
| s_info | 1 | idx_asw | 3 | weight | A | NULL | NULL | NULL | YES | BTREE | | | YES |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.00 sec)
1.索引老大不能空：当使用复合索引时，如果第一个没有使用的话后面的也会失效
# 分析使用索引查询 type为ref，key_len为4
mysql> explain select sex from s_info where s_id=1;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s_info | NULL | ref | idx_asw | idx_asw | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 当使用age，没有使用s_id 时 可见并没有使用到索引
mysql> explain select sex from s_info where age=99;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | s_info | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 中间like、range，后面失效
-- 使用索引查询
mysql> explain select sex from s_info where age=12 and s_id=1 and weight=99;
+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | s_info | NULL | ref | idx_asw | idx_asw | 14 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可见在age使用范围查询之后索引长度变短，并且类型变为了range
mysql> explain select sex from s_info where age>11 and s_id=1 and weight=99;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s_info | NULL | range | idx_asw | idx_asw | 9 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
事务ACID
mysql客户端默认使用事务；
- 原子性： 事务要么不执行要么全部执行；
- 一致性： 事务执行失败就会回滚到上一次的状态；
- 持久性： 只要事务提交数据就会持久保存；
- 隔离性： 多个事务之间为独立的事件；四个隔离级别：
- 读未提交：一个事务修改数据，未提交。另一事务中可以读取已修改数据。可能脏读。
- 读已提交：一个事务修改了数据，已提交。另一事务中可以读取到已修改数据。不可重复读。
- 可重复读（默认级别）：一个事务修改了数据，并且已提交。另一事务中不可读读取到已修改数据。可能幻读。
- 串行化：一个事务对数据进行操作时会加锁，另一事务等待锁释放。避免幻读，不可重复读，脏读，但是效率低。
-- 开启事务
start transaction; # 或者 begin;
-- 保存点
savepoint save_name;
-- 回滚
rollback [name];
-- 提交事务
commit;
-- 查看隔离级别
select @@tx_isolation;
show global variables like '%isolation%';
-- 设置隔离级别
-- read-committed 读已提交
-- read-uncommitted 读未提交
-- repeteable-read 可重复读
-- serializable 串行化
set global transaction_isolation ='read-committed';
数据库引擎
- InnoDB
- MyIsam
- CSV
- Memory
扫一扫
8万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
