[TOC]

小把戏：

1.查看系统是否区分大小写

1
show variables like 'lower_case_table_names';
  1. lower_case_table_names = 0
    • 表名在存储和比较时都是区分大小写的。
  2. lower_case_table_names = 1
    • 表名在存储和比较时都会被转换为小写。这意味着，如果你创建了一个名为 MyTable 的表，MySQL 会将其存储为 mytable，并且在引用它时也必须使用小写。
    • 这个设置通常在跨平台环境中使用，以确保表名的一致性，特别是当数据库在 Unix/Linux 和 Windows 之间迁移时。
  3. lower_case_table_names = 2
    • 表名在存储时保持原样，但在比较时会被转换为小写。在 MySQL 5.6.3 及之后的版本中已被弃用，因为它可能导致混淆和不可预测的行为。

真常用：

DDL（数据定义）

  • 对库或者表进行操作的语句
创建数据库：
1
create database db01;
查看数据库(DQL)
1
show databases;
查看建库语句
1
show create database db01;
创建数据库的时候添加属性
1
create database db02 charset utf8;
删除数据库
1
drop database db02;
修改定义库
1
alter database db01 charset utf8;
创建表(范例1)
1
2
3
4
5
6
7
create table student1(
sid int,
sname varchar(20),
sage tinyint,
sgender enum('m','f'),
comtime datetime
);
创建表(范例2)
1
2
3
4
5
6
7
create table student2(
sid int comment '学号',
sname varchar(20) not null comment '学生姓名',
sage tinyint unsigned not null comment '年龄',
sgender enum('m','f') not null default 'm' comment '学生性别',
cometime datetime not null comment '入学时间'
)charset utf8 engine innodb;
查看建表语句
1
show create table student2;
查看表
1
show tables;
查看表中列的定义信息（推荐）
1
desc student2;
删除表
1
drop table student2;
修改表名
1
alter table student rename stu;
添加列（或多行列）和列数据类型的定义
1
2
alter table stu add age int;
alter table stu add test varchar(20),add qq int;
指定位置进行添加列
1
2
3
4
# 表首
alter table stu add classid varchar(20) first;
# 指定列
alter table stu add phone2 int after sid;
删除指定的列及定义
1
alter table stu drop qq;
修改列及定义（列属性）
1
alter table stu modify sid varchar(20);
修改列及定义（列名及属性)
1
alter table stu change phone telphone int();

DCL（数据控制）

  • DCL是针对权限进行控制
  1. 授权
1
2
3
4
5
6
7
8
9
grant all on *.* to root@'192.168.175.%' identified by '123456';
# 授予root@'192.168.175.%'用户所有权限(非超级管理员)
grant all on *.* to root@'192.168.175.%' identified by '123456' with grant option;
# 授权一个超级管路员
with
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量
  1. 收回权限
1
2
3
4
revoke select on *.* from root@'192.168.175.%';
# 收回select权限
show grants for root@'192.168.175.%';
# 查看权限
  1. with权限列举
1
ALTER USER 'root'@'192.168.175.%' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 500;

这将限制'root'@'192.168.175.%'用户每小时最多执行1000个查询和500个更新操作。

DML(数据操作)

  • 操作表中的数据
更新数据
  1. 规范update修改
1
update student set sgender='f' where sid=1;
  1. 如果非要全表修改
1
update student set sgender='f' where 1=1;
删除数据
1
delete from student where sid=3;
删除表
1
truncate table student;

以下是关于TRUNCATE TABLE的一些关键点：

  1. 速度：通常，TRUNCATE TABLE比DELETE命令更快，因为它不记录任何单独的行删除操作。
  2. 触发器：TRUNCATE TABLE不会触发与行删除相关的DELETE触发器。
  3. 权限：执行TRUNCATE TABLE通常需要比执行DELETE更高的权限。
  4. 锁定：TRUNCATE TABLE通常会锁定表以防止在删除过程中进行其他操作。这可能会导致其他尝试访问该表的操作失败或被阻塞。

DQL（数据查询）

  • select：基础用法

    SELECT DISTINCT 字段1,字段2… FROM 表名

    WHERE 条件

    GROUP BY field 【列名】

    HAVING 筛选

    ORDER BY field

    LIMIT 限制条数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    mysql -uroot -p123 < world.sql
    # 常用用法
    select countrycode,district from city;
    # 常用用法
    select countrycode from city;
    # 查询单列
    select countrycode,district from city limit 2;
    select id,countrycode,district from city limit 2,2;
    # 行级查询
    select name,population from city where countrycode='CHN';
    # 条件查询
    select name,population from city where countrycode='CHN' and district='heilongjiang';
    # 多条件查询
    select name,population,countrycode from city where countrycode like '%H%' limit 10;
    # 模糊查询
    select id,name,population,countrycode from city order by countrycode limit 10;
    # 排序查询（顺序）
    select id,name,population,countrycode from city order by countrycode desc limit 10;
    # 排序查询（倒序 desc 正序 asc）
    select * from city where population>=1410000;
    # 范围查询(>,<,>=,<=,<>)
    select * from city where countrycode='CHN' or countrycode='USA';
    # 范围查询OR语句
    select * from city where countrycode in ('CHN','USA');
    # 范围查询IN语句
    select country.name,city.name,city.population,country.code from city,country where city.countrycode=country.code and city.population < 100;
    # 多表查询