MySQL *** 作命令

SQL分类

1、DDL-数据库基本 *** 作命令

show  databases；        查看当前的数据库

use  数据库名；        切换/使用指定名字的数据库

select  database( )；        查看当前正在使用的数据库

创建数据库：

create  database  [ if not exists ]  数据库名  [ default  charset  字符集 ] [ collate排序规则 ] ；

创建数据库：
create database mydb default charset utf8bm4 utf8_general_ci; 

排序规则命名约定：以其相关的字符集名开始，中间包括一个语言名，
并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。
例如，系统使用utf8字符集，若使用utf8_bin校对规则执行SQL查询时区分大小写，
使用utf8_general_ci不区分大小写(默认的utf8字符集对应的校对规则是utf8_general_ci

drop  database  [ if exists ]  数据库名 ；        删除数据库

2、DDL-数据表 *** 作

show  tables ;        查看所有表

desc  表名 ;        查看表结构

show  create  table  表名 ;        查看建表的sql语句

创建表：

create  table  表名 (

        字段名1  字段1类型  [ 约束 ] [ comment  ‘字段1注释’ ] ,

        字段名2  字段2类型  [ 约束 ] [ comment  ‘字段2注释’ ] ,

        字段名3  字段3类型  [ 约束 ] [ comment  ‘字段3注释’ ] ,

         ......

        [ CONSTRAINT ]  [ 外键名 ]  FOREIGN  KEY(外键字段名字)  REFERENCES  主表名(主表列名)

)  comment  ‘表注释’;

创建用户表：
create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age < 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表'

修改表的字段属性：

alter  table  表名  modify  字段名   新的字段类型(长度)   [约束]  [comment  '字段描述’];

# 把原来用户表的varchar类型的gender改成char类型
alter table user modify gender char(1) 

# 把字段设置为自增
alter table emp modify id int auto_increment 

给表新增字段：

alter  table  表名  add  新增字段名  新增字段的类型(长度)  [约束] [comment  '字段描述'] ;

alter table  user  add  class_id  int  unique  not null  comment  '班级ID'

修改表的字段名：

alter  table  表名  change  旧字段名  新的字段名  新的字段类型(长度)  [约束] [comment];

alter table user change gender sex char(1)  not null default '男' comment '性别'

删除字段：

alter  table  表名   drop  字段名；

修改表名字：

alter  table  表名  rename  to  新的表名；

删除表：

1.  drop   [ if  exists ]   表名 ；

2.  truncate  table  表名 ；    这种方式删除表又重新创建一样的表，相当于只清空表数据。

增加外键：

alter  table  表名  add  constraint  外键名  foreign key(外键字段名)  references  主表名(主表列名)；

alter table emp add constraint fk_emp_dept_id  foreign key(dept_id)  references dept(id)

插入数据：

1.插住指定字段名的值

insert  into  表名(字段1,字段2,字段3,...)   values(值1,值2,值3,...)；

2.插入全部字段的值

insert  into  表名  values(值1,值2,值3,...)

3.批量插入指定字段的值

insert  into  表名(字段1,字段2,...)  values(值1,值2,...),(值1,值2,...)

4.批量插入全部字段的值

insert  into  表名  values(值1,值2,...),(值1,值2,...)

！插入数据时，指定字段的顺序要与值的顺序一一对应且类型一致，字段大小也要在规定范围内；字符串和日期类型数据应该包含在引号中。

更新表数据：一般都要加where条件，否则全部数据都会受影响

update   表名  set  字段名1=值1，字段名2=值2,...  [where 条件]

删除表数据：不加where条件会全部数据删除

delete  from  表名  [ where 条件 ]

 3、DCL-管理用户

#查看用户
select * from user;

# 创建用户，user01,只能够在当前主机localhost访问，密码123456
create user 'user01'@'localhost' identified by '123456';

#创建用户 user02 ,可以在任意主机访问，密码123456
create user 'user01'@'%' identified by '123456';

#修改用户 user01 的访问密码为1234
alter user 'user01'@'%' identified with mysql_native_password by '1234'

#删除user01@localhost
drop user 'user01'@'localhost'

 4、DQL-数据查询语言

a.基本查询：

1、查询多个字段

select  *  from  表名 ；

select  字段1，字段2，...  from  表名；

2、设置别名

select  字段1 [ as  别名1] ，字段2  [ as  别名2] ，...  from  表名；

3、去除重复

select  distinct  字段列名  from  表名 ；

b.条件查询：

where之模糊查询 like：

like后跟：

%    表示多个任意字符

_     表示一个任意字符

where之逻辑运算符：

and、or、not

where之范围查询：

in  表示在一个非连续的范围内

between  ...  and  ...        表示在一个连续的范围内

where 之空值判断：

判断为空  is null

判断非空  is not null

null 和 ' ' 是不一样的

排序查询 order by ：

分组查询 group by：

分组一般搭配聚合函数count、max、min、sum、avg使用：

分组技巧，显示分组后的住在深圳或广州的人有哪些, 用到关键字group_concat(字段名)

分页查询：

行转列的应用：

SELECT name ,

  SUM(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,

  SUM(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,

  SUM(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语

FROM SCORES

GROUP BY name;

参考文档：https://www.w3xue.com/exp/article/201812/12440.html

---

实践练习：

# 条件查询练习

-- 使用 as 给字段起别名 --
select name from students;
select name as '姓名' from students;


-- 消除重复行（查性别）--
select distinct gender from students;


# where 之逻辑运算符

-- and
-- 18和28之间的学生信息
select * from students where age>18 and age<28;

失败：select * from students where 18 < age < 28 ; 没有这个写法

-- 18岁以上的女性
select * from students where age>18 and gender='女';

-- or 
-- 18岁以上或者身高180以上(包含)的女性
select * from students where age>18 or height>=180;

-- not
-- 不在18岁以上的女性
select * from students where not (age>18 and gender='女');



# 排序查询
-- 查询年龄在18到34岁之间的男性，身高从矮到高排序（默认是sc升序）
select * from students where (age between 18 and 34)and gender=1;
select * from students where (age between 18 and 34)and gender=1 order by height asc;

-- 查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where (age between 18 and 34)and gender=2 order by height desc;

-- order by多个字段
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从大到小排序
select * from students where (age between:18 and 34)and gender=2 order by height desc,age desc;