Mysql 数据库 学习笔记

# 常用操作

SHOW DATABASES;    # 查看所有库

SELECT DATABASE();    # 查看当前连接的库

SHOW TABLES;    # 查看当前库下的所有表

SHOW CREATE DATABASE test;    # 查看创建库时的语句

SHOW GRANTS FOR 'root'@'localhost';    # 查询用户权限

SHOW CREATE TABLE test_table;    # 查看创建表时的语

DESC test_table; # 查看表结构

CREATE DATABASE IF NOT EXISTS easylinux DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;    # 创建数据库 并指定默认的字符集是utf8

use test;    # 选中数据库
  # 通告MySQL把test数据库作为默认当前数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到出现下一个不同的use语句

DROP DATABASE test;    # 删除数据库

DROP TABLE test_table;    # 删除表

Delete FROM mysql.user Where User='service' and Host='%';    #删除用户

mysqladmin -u root -p password'P@ssw0rd'    # 修改用户登录密码

GRANT ALL PRIVILEGES ON easylinux.* TO easylinux@'%' IDENTIFIED BY 'P@ssw0rd';    # 创建用户授权指定数据库中所有表数据的权限

GRANT SELECT,INSERT,UPDATE,DELETE ON easylinux.* TO easylinux@'%' IDENTIFIED BY 'P@ssw0rd';    # 创建用户授权指定数据库 查询 插入 更新 删除 所有表数据的权限

FLUSH PRIVILEGES;    # 刷新权限

ALTER DATABASE test CHARACTER SET GBK COLLATE GBK_CHINESE_CI;    # 修改库编码字符集,不推荐使用

RENAME TABLE test_table TO test_table_1;    # 重命名表名

ALTER TABLE test_table ADD note VARCHAR(60);    # 表中添加字段

ALTER TABLE test_table DROP note;    # 表中删除字段

ALTER TABLE test_table CHANGE password passwd VARCHAR(60);    # 重命名表中的字段

ALTER TABLE test_table CHANGE passwd passwd VARCHAR(20); # 修改表中的字段类型

# 创建表

CREATE TABLE test_table(
    name VARCHAR(40) PRIMARY KEY,
    email VARCHAR(40),
    password VARCHAR(40)
);

# 增加一条数据

INSERT INTO `test_table`(name, email, password) VALUES('jeff1', '269728310@qq.com', '1');

INSERT INTO `test_table`(name, password) VALUES('jeff2', '2');

INSERT INTO `test_table` SET name = 'jeff3';

INSERT INTO `test_table` VALUES('jeff4', '269728310@qq.com', '4');

# 增加多条数据

INSERT INTO `test_table`(name, email, password)
    VALUES('jeff5', '269728310@qq.com', '5'),
          ('jeff6', '269728310@qq.com', '6'),
          ('jeff7', '269728310@qq.com', '7');

# 常用属性

PRIMARY KEY # 主键
AUTO_INCREMENT    # 自增
COMMENT 'easylinux'    # 注译
DEFAULT 'easylinux'    # 默认值1
NOT NONE    # 不能为空
UNIQUE    #唯一键

# 常用字段类型

# 日期和时间数据类型
 date # 日期，格式：2014-09-18
 time # 时间，格式：08:42:30
 datetime # 日期时间，格式：2014-09-18 08:42:30
 timestamp # 自动存储记录修改的时间
 year # 年份
 
# 整型
 tinyint # 范围（-128~127）
 smallint # 范围（-32768~32767）
 mediumint # 范围（-8388608~8388607）
 int # -2147483648~2147483647）
 bigint # 范围（+-9.22*10的18次方）
 
# 浮点型
 float(m, d) # 单精度浮点型，m总个数，d小数位
 double(m, d) # 双精度浮点型，m总个数，d小数位
 decimal(m, d) # decimal是存储为字符串的浮点数
 
# 字符串数据类型
 char(n) # 固定长度，最多255个字符
 varchar(n) # 可变长度，最多65535个字符
 tinytext # 可变长度，最多255个字符
 text # 可变长度，最多65535个字符
 mediumtext # 可变长度，最多2的24次方-1个字符
 longtext # 可变长度，最多2的32次方-1个字符

# 常用关系运算符

=    等于
>    大于
>=   等于等于
<    小于
<=   小于等于
!=   不等于

关系运算符需要放在 WHERE后面
SELECT 字段1, 字段2... FROM 表名 [WHERE 条件]

# 常用逻辑运算符

and  与
or   或
not  非

# 常用算术运算符

+    加法运算
-    减法运算
*    乘法运算
/    除法运算，返回商
%    求余运算，返回余数

# 例如  UPDATE `test_table` SET password = password+1 WHERE name = 'jeff5';

# 查询

SELECT * FROM `test_table`;    # * 代表查询某个表中所有字段

SELECT name, email From `test_table`;     # * 代表查询某个表中name, email 字段

SELECT name FROM `test_table` WHERE name = 'jeff4';    # 查询name等于jeff4的字段

SELECT name AS username, email AS mail FROM `test_table` WHERE name = 'jeff4';   # AS 给字段取别名

SELECT name, password FROM `test_table` AS t WHERE name = 'jeff4'; # AS给表取别名

SELECT t.name, t.password FROM `test_table` AS t WHERE name = 'jeff4'; # AS给表取别名

SELECT name username FROM `test_table` WHERE name = 'jeff4';   # 给字段取别名偷懒方式

SELECT * FROM `test_table` WHERE email='269728310@qq.com' and password >= 6;    #查询 邮箱=123456，并且密码>=6

SELECT * FROM `test_table` WHERE email='269728310@qq.com' OR password >= 2;    #查询 邮箱=269728310@qq.com，或密码>=2

# 同时查询多条记录

SELECT * FROM test_table1 WHERE id IN(1,3,4);    #判断在IN()中的列表
SELECT * FROM test_table1 WHERE id NOT IN(1,3,4);    #判断不在IN()中的列表

# 升降序查询

SELECT * FROM test_table1 ORDER BY id ASC;    # 升序

SELECT * FROM test_table1 ORDER BY id DESC;    # 降序

# 聚合函数

SELECT MAX(id) FROM test_table;    # 找出最大值

SELECT MIN(id) FROM test_table;    # 找出最小值

SELECT AVG(id) FROM test_table;    # 求平均数

SELECT SUM(id) FROM test_table;    # 求和

SELECT COUNT(id) FROM test_table;    # 匹配指定条件的行数

# 常用函数

# 时间函数
  SELECT NOW();    # 2018-04-24 14:37:09
  SELECT CURDATE();    # 2018-04-24
  SELECT CURTIME();    # 14:39:37

# 数学函数
  SELECT CEIL(2.3);    # 向上取舍
  SELECT FLOOR(2.3);    # 向下取舍
  SELECT RAND();    # 返回0~1之间的小数
  SELECT CEIL(RAND()*10000);    # 随机生成4位数字

# 修改

UPDATE `test_table` SET name = 'jeff4_update' WHERE name = 'jeff4';    # 查询name=jeff4改为name=jeff4_update

UPDATE `test_table` SET name = 'jeff5_update', password = '4' WHERE name = 'jeff5'; # 查询name=jeff5行修改字段name=jeff5_update password=4

UPDATE `test_table` SET password = 5 WHERE password = 4 and name = 'jeff5_update'; # 查询name=jeff5_update,password=4 行修改字段password=5

# 删除

DELETE FROM `test_table` WHERE password = '123456'; # 删除所有 password=123456 的行

TRUNCATE TABLE test_table;    # 删除整张表内容，如果有自增字段重新计数

# 查看mysql当前连接数

show global status like 'Threads_connected';

# 查看mysql最大连接数

show variables like 'max_connections';

# 查看mysql历史最大连接数

show global status like 'Max_used_connections';

# 查看mysql锁表数

mysql -uroot -p -e "show processlist;" |grep "Waiting for table level lock" |wc -l

# 查看mysql慢查询

mysql -uroot -p -e "show processlist;" |grep "Sending data" |awk '{if($6>4){print 1}}'