MySQL

创建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';

主键&外键

当我们创建表的时候需要对另一表进行关联,则可以同过创建外键与另一表的主键进行关联,即可对两张表格进行关联并起约束作用。

主键: 非空、唯一、索引
外键: 可以为空但得和主键对应、不唯一、索引

  1. 通过创建表的方式创建主键
create table students (
	s_id int primary key,
	name varchar(8)
);
  1. 通过创建表的方式创建外键
create table s_info (
	s_id int primary key ,
    age int,
    sex enum('男', '女', '保密'),
    constraint fk_id foreign key(s_id) references students(s_id)
    );
  1. 直接删除主表
    外键所在的表格为从表,主键所在的表为主表,如果要删除主表,需要先保证其主键没有对应的外键,即需要先删除从表,或者外键,否则会报错如下。很好的保证了表的完整性。
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)
  1. 中间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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值