SQL常用命令（MYSQL数据库）

SQL命令合集（转载）：
http://istester.com/mysql/515.html

环境安装

1.mysql环境安装：
http://istester.com/linux/258.html

2.登录mysql
mysql -u root –p

第1天作业

1）创建数据库 istester
2）查看数据库创建是否成功

创建数据库
create database isTester;

查看数据库
show databases;

备注：
登录mysql：mysql -u root –p
sql语句注意加;

第2天作业

创建表istester和表idoxu:

1. istester表的字段、类型、属性：
   id INT(10) NOT NULL UNIQUE PRIMARY KEY,
   uname VARCHAR(20) NOT NULL,
   sex VARCHAR(4),
   birth YEAR,
   department VARCHAR(20),
   address VARCHAR(50),
   idoxu VARCHAR(20)

CREATE TABLE istester(
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
uname VARCHAR(20) NOT NULL,
sex VARCHAR(4),
birth YEAR,
department VARCHAR(20),
address VARCHAR(50),
idoxu VARCHAR(20)
);

2. idoxu表的字段、类型、属性：
   id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
   stu_id INT(10) NOT NULL,
   c_name VARCHAR(20),
   istester VARCHAR(50),
   grade INT(10)
   CREATE TABLE idoxu(
   id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
   stu_id INT(10) NOT NULL,
   c_name VARCHAR(20),
   istester VARCHAR(50),
   grade INT(10)
   );
   查看表：
   desc idoxu;
   使用哪个数据库：
   show databases;
   use isTester; (用use database名切换要到使用的database下)

第3天作业

根据已有的表istester 和 idoxu 创建新表（复制表）
1）复制istester表结构，创建新表istester2
注：可以有两种方式
create table istester2 like istester;
create table istester2 as select * from istester where 2=1;

2）复制idoxu表结构和数据，创建新表idoxu2
create table idoxu2 as select * from idoxu;

3）复制idoxu表结构的 id,stu_id,istester三个字段，创建新表idoxu3
create table idoxu3 as select id,stu_id,istester from idoxu;

第4天作业

1） 删除表 istester2
drop table if exists istester2;

2） 同时删除表idoxu2和idoxu3
drop table idoxu2, idoxu3;

3）查看还有哪些表
show tables;

删除表的语法：
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 …]
drop 是直接将表格删除，无法找回。例如删除 user 表：

删除表中数据：
delete from tb (where);delete 是删除表中数据，但可以与where连用，删除特定行；
– 删除表中所有数据delete from user;
– 删除指定行delete from user where username =‘Tom’;

第5天作业

1）修改istester表，添加一个字段istester6 （字符类型VARCHAR，长度100，不允许为空）
alter table istester add column istester6 VARCHAR(100) NOT NULL;

2）查看字段istester6 ，是否添加成功
desc istester;

3）修改istester表，删除字段istester6
alter table istester drop istester6;

4）确认字段istester6 ，是否删除成功
desc istester;

MySQL 使用 ALTER TABLE 语句修改表。
常用的语法格式如下：
ALTER TABLE <表名> [修改选项]
添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型>
alter table istester add column istester6 VARCHAR(100) NOT NULL;

修改字段数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
alter table istester modify uname VARCHAR(50);

删除字段
ALTER TABLE <表名> DROP <字段名>；
alter table istester drop istester6;

修改字段名称
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
alter table istester uname name VARCHAR(30);

修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>；
alter table istester rename istester1;

第6天作业

插入 insert：

1）向表istester插入数据
id = 1 ,uname = idoxu ,idoxu = 2020
id = 2 ,uname = idoxu2 ,idoxu = 2020

id=11,uname = idoxu3,sex=1
id=12,uname = idoxu4,sex=2
id=13,uname = idoxu5,sex=1
id=14,uname = idoxu6,sex=2

insert into istester(id,uname,idoxu) values(1,“idoxu”,“2020”);
insert into istester(id,uname,idoxu) values(2,“idoxu2”,“2020”);
insert into istester(id,uname,sex)
values(11,“idoxu3”,1),(12,“idoxu4”,2),(13,“idoxu5”,1),(14,“idoxu6”,2);

2）向表idoxu插入数据
id=4,stu_id=11,c_name=idoxu,grade=90
id=5,stu_id=12,c_name=lin,grade=100
id=6,stu_id=33,c_name=istester,grade=20

insert into idoxu(id,stu_id,c_name,grade)
values(4,11,“idoxu”,90),(5,12,“lin”,100),(6,33,“istester”,20);

插入数据insert:
INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );
INSERT INTO可以插入单行或多行数据，当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。
insert into istester(id,uname,idoxu) values(2,“idoxu2”,“2020”);
insert into istester(id,uname,sex)
values(11,“idoxu3”,1),(12,“idoxu4”,2),(13,“idoxu5”,1),(14,“idoxu6”,2);

读取数据表：
select * from istester;

修改表中的数据：
update 表名 set 字段名=‘新内容’ + where条件
update istester set uname=“idoxu2” where id=2;

删除表中的数据：
delete from istester;

第7天作业

1）查询istester表 id = 1 的内容
select * from istester where id=1;

2）查询idoxu 表 grade=100 的内容
select * from idoxu where grade=100;

第8天作业

继续 select

1. 查找idoxu表，名称（c_name）包含 “i” 的数据
   select * from idoxu where c_name like “%i%”;

2. 查找istester表，id 包含 “1” 的数据
   select * from istester where id like “%1%”;

3. 查找istester表，id 包含 “1” 的数据，按id降序
   select * from istester where id like “%1%” order by id desc;

4）查找istester表，id 包含 “1” 的数据 ，取id最大的三个
select * from istester where id like “%1%” order by id desc limit 3;

select查询语句方法，见文章：https://www.cnblogs.com/sweet-i/p/11391490.html

第9天作业

1） 找出idoxu表中，分数最高的同学和分数
select c_name,grade from idoxu where grade in (select max(grade) from idoxu);

2） 找出idoxu表中，分数最低的同学和分数
select c_name,grade from idoxu where grade in (select min(grade) from idoxu);

第10天作业

1)找出istester表，sex为空的的数据；
select * from istester where sex is NULL;

2）更新istester表，把sex为空的，设置为0（性别未知）
update istester set sex = 0 where sex is NULL;

3）找出idoxu表，grade小于60分的同学
select * from idoxu where grade < 60;

4）更新idoxu表，把grade小于60分的同学，一律改为59分
update idoxu set grade = 59 where grade < 60;

修改表中的数据：
update 表名 set 字段名=‘新内容’+ where条件

第11天作业

1）查找istester表，按id降序
select * from istester order by id desc;

2）查找idoxu表，按grade升序
select * from idoxu order by grade asc;
select * from idoxu order by grade;

第12天作业

1）查询istester表，有多少条数据
select count(*) from istester;
select count(id) from istester;

2）查询istester表，有几种性别类型（sex字段，去重）
select count(distinct(sex)) from istester;

总结count:
（1）count(*)—包括所有列，返回表中的记录数
（2）count(列名)—只包括列名指定列，返回指定列的记录数,会忽略NULL的记录
（3）count(distinct列名)—只包括列名指定列，返回指定列的不同值的记录数，去重
去重聚合获取count值：SELECT count(DISTINCT(字段)) FROM 表
带null去重聚合count值：SELECT count(DISTINCT(IFNULL(字段,1))) FROM 表

第13天作业

1） 查找idoxu表，学生成绩(grade) 总分
select sum(grade) as sum_grade from idoxu;

2） 查找idoxu表，学生成绩(grade) 平均分
select avg(grade) as avg_grade from idoxu;

总结：
Mysql聚集函数：
（1）AVG() 函数 计算平均数
（2）COUNT() 函数 计算总行数
（3）MAX() 函数 ： 返回指定列的最大值
（4）MIN() 函数 : 返回指定列的最小值
（5）SUM()函数 ： 返回指定列值的和

第14天作业

1）查找idoxu表，成绩在80 - 100区间的学生 ；
select * from idoxu where grade between 80 and 100;

2）查找istester表，id 为 2，11，12 的数据 ；
select * from istester where id in (2,11,12);

第15天作业

1）删除 istester表，id大于12的数据 ；
delete from istester where id > 12;

2）删除idoxu表，分数grade不及格（小于60分）的数据 ；
delete from idoxu where grade < 60;

使用 DELETE 语句从单个表中删除数据，语法格式为：
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
• <表名>：指定要删除数据的表名。
• ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
• WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
• LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

第16天作业

1）造数据 ，把istester表的所有数据，插入到 idoxu表
字段取值规则
id 取id
stu_id 取id
c_name 取 uname
istester 和 grade字段，给默认值 60

insert into idoxu(id,stu_id,c_name,istester,grade) select id,id,uname,60,60 from istester;

注：mysql 一张表的数据插入另一张表的sql语句
1.表结构完全一样
insert into 表1 select * from 表2
2. 表结构不一样（这种情况下得指定列名）
insert into 表(列名1,列名2,列名3) select 列1,列2,列3 from 表2

第17天作业

1）修改表idoxu，把字段istester ，改为istester6（字符类型varchar，长度160 ）
alter table idoxu change istester istester6 varchar(160);

2）检查是否修改成功
desc idoxu;

3）把idoxu表，改名为 idoxu6
alter table idoxu rename to idoxu6;

4）检查是否修改成功
show tables;

修改字段名称
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型;
修改表名：
RENAME TABLE <旧表名> TO <新表名>;
ALTER TABLE <旧表名> RENAME TO <新表名>;

第18天作业

1）创建数据库 istesterdb6
create database istesterdb6;

2）检查数据库是否创建成功 ；
show databases;

3）进入istesterdb6 库
use istesterdb6;

4）在数据库 istesterdb6 ，创建idoxu表，直接拷贝 istester库idoxu6表的数据和结构 ；
create table idoxu as select * from isTester.idoxu6;
或create table istesterdb6.idoxu as select * from isTester.idoxu6;

5）检查表是否创建成功 ；
show tables;

第19天作业

idoxu表，分数grade
1） 排名前三的学生 和分数 ；
select c_name,grade from idoxu order by grade desc limit 3;

2）排名3 - 6名的学生 和分数 ；
select c_name,grade from idoxu order by grade desc limit 2,4;

3）排名6名以后的所有学生 和分数 ；
select c_name,grade from idoxu order by grade desc limit 5,999;

LIMIT基本语法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset;
LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0。

第20天作业

多表（左连接、右连接、内连接）
表 idoxu6 和 表istester
#左连接
select * from idoxu6 a left join istester b on a.id=b.id;

#右连接
select * from idoxu6 a right join istester b on a.id=b.id;

#内连接
select * from idoxu6 a inner join istester b on a.id=b.id;

内连接查询 inner join
语句：select * from a inner join b on a.name=b.name
说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集部分。

左连接查询 left join
语句：select * from a left join b on a.name=b.name
说明： 左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。即相当于搜索左表的差集

右连接 right join
语句：select * from a right join b on a.name=b.name
说明：右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

第21天作业

1)把表idoxu6，改完idoxu
alter table idoxu6 rename to idoxu;

2)删除库 istesterdb6
drop database istesterdb6;

3)删除istester表
drop table istester;

4)清空idoxu表的数据
delete from idoxu;