MySQL SQL 速查

create database 软件3班; #创建数据库

show databases; #显示其中所有数据库名

Use 软件3班; #选择数据库

create DATABASE 软件4班 DEFAULT CHARACTER SET GB2312 DEFAULT collate GB2312_chinese_ci;

show create database 软件4班;

show create database 软件3班;

CREATE DATABASE `软件3班` /*!40100 DEFAULT CHARACTER SET latin1 */

alter DATABASE 软件3班 DEFAULT CHARACTER SET GB2312;

alter DATABASE 软件3班 DEFAULT collate GB2312_chinese_ci;

alter DATABASE 软件3班 DEFAULT CHARACTER SET GB2312 DEFAULT collate GB2312_chinese_ci;

drop database 软件3班;

show databases;

create database 库名 [DEFAULT CHARACTER SET 字符集]

alter DATABASE 库名 DEFAULT CHARACTER SET 字符集名 DEFAULT collate 排序规则名;

show create database 库名;

Use 软件4班; #切换数据库到“软件4班”

create table 学生表

create table 学生表1(学号 varchar(3) not null,姓名 varchar(4), 性别 varchar(1),年龄 int, 生日 date, 成绩 float);

ALTER TABLE 学生表1 ADD PRIMARY KEY (学号);

create table 学生表2 (学号 varchar(3), 姓名 varchar(4), 性别 varchar(1),年龄 int, 生日 date, 成绩 float, primary key (学号));

show create table 学生表2;

create table 学生表3

create table 学生表4

ALTER TABLE 学生表4 ALTER 性别 SET DEFAULT ‘女’;

show create table 表名;

Alter table 旧表名 rename 新表名；

Alter table 学生表 rename 学生信息表;

Alter table 表名 change 原字段名 新字段名 数据类型定义

Alter table 表名 change 字段名 字段名 新数据类型

SHOW CREATE table 学生表;

Alter table 表名 modify 字段名 数据类型定义

Alter table 表名 add 字段名 数据类型

Alter table 学生表 add 籍贯 varchar(10);

Alter table 表名 drop 字段名

Alter table 学生表 drop 籍贯;

alter table 表名 drop primary key;

alter table 学生表 drop primary key;

alter table 表名 add primary key (字段名);

alter table 学生表 add primary key (学号);

alter table 表名 modify 主键字段 数据类型 primary key;

Alter table表名 change 主键字段 主键字段 数据类型 primary key;

alter table 学生表 modify 学号 varchar(3) primary key;

Alter table 学生表 change 学号 学号 varchar(3) primary key;

Drop table 表名

Drop table 学生表3;

如：Alter table 学生表 add 籍贯 varchar(10);

Update 表名 set 字段名=值 [where 条件]

Update student set pwd=’ABCDEFG’ where stuname=’ 林斌’

update 表名 set 字段名1=新值1，字段名2=新值2 where 条件

Update student set pwd=’ 88888888’，classno=’20000002’ where stuname=’ 林斌’

Update student set pwd=’12345678’

Update student set 成绩=80

Update student set 成绩=成绩+5 where ClassNo='20000001'

delete from 表名 [where 条件]

Delete from 表名 where 条件

Delete from 表名

Insert into 目的表 select * from 源表

Insert into 学生表2 select * from student;

如：insert into 学生表3(stuno,stuname) select stuno,stuname from student;

insert into 学生 select * from student where classno='20000001';

INSERT INTO l VALUES ('1', '2'), ('3', '4');

INSERT INTO R VALUES (‘5', ‘6‘, ’7’), (‘8', ‘9‘, ’0’), (‘a', ‘b‘, ’c’) ;

update r set e='2';

修改数据：update r set A=‘3';

Update 表名 set 字段名=新值 where 子查询；

update student set pwd='123456' where ClassNo in (select classno from class where ClassName like '00%');

delete from 表名 where 子查询；

delete from student where ClassNo in (select classno from class where ClassName like '00%');

insert into 表2(字段1, 字段2,……) select 字段1, 字段2,…… from 表1 where 条件

insert into 得票表(姓名,性别) select 姓名,性别 from 选举表

update student set stuname=ltrim(stuname)

update student set stuname=rtrim(stuname)

update student set stuname=replace(stuname,'丽','莉') where stuname like '%丽%'

update student set stuname=replace(stuname,'莉','丽') where stuname like '%莉%'

update student set stuname=concat('中国',rtrim(StuName));

update student set stuname=replace(stuname,'中国','') where stuname like '%中国%‘

Sum(字段名)

select sum(credit) as '学分和' from course

AVG(字段名)

select avg(credit) as '平均值' from course

Max(字段名)

select max(credit) as '最大值' from course

Select max(teacher) as '最大值' from course

Min(字段名)

select min(credit) as '最小值' from course

Select min(teacher) as '最小值' from course

Count(字段名)， 也可以用 count(*)

select count(credit) as '行数' from course where credit=2

select count(*) as '总行数' from course

Select 字段名 from 表名 group by 字段名

select kind, count(*) as '计数' from course group by kind

Select kind,sum(credit) as '学分和' from course group by kind

select kind,avg(credit) as '学分平均' from course group by kind

select kind,count(*) as '课程数',sum(credit) as '学分和', avg(credit) as '学分平均' from course group by kind

select kind, SUM(credit) as '学分和' from course group by kind having sum(credit)>9

或select classname,stuno,stuname from class join student on class.ClassNo=student.ClassNo;

select a,b from A union select c,d from B;

select classno,classname from class union select classno,stuname from student;

Select 字段列表 from 左表 cross join 右表

Select * FROM L CROSS JOIN R

Select 字段列表 from 左表 join 右表 on 条件

select * from l join r;

select * from l join r on a>1;

select A,B,C from l join r on a>1 and c='8';

select * from l join r on a>1 where c='8';

等同于select * from l join r on a>1 and c='8';

select classname,stuname,pwd from student join class on student.classno=class.ClassNo;

select * from l join r on b>e;

select * from l join r on a<e;

select L.A, B,C,D,R.A as 'ra' from L join R on R.A=L.A;

再看自然连接：select L.A, B,C,D from L join R on R.A=L.A;

select * from l left join r on 1; （#on表示为真）

select L.A, B,C,D,R.A as 'ra' from L left join R on L.A=2;

select * from l right join r ON 1;

select L.A, B,C,D,R.A as 'ra' from L right join R on L.A=2;

select * from L full join R on 1;

select a,b from l union select c,d from r;

select * from student where stuname='林斌‘

select 字段列表 from 表名 [where 条件]

select * from student;

Select classno, stuname from student;

Select * from student where classno='20000002';

Select classno, stuname from student where classno='20000002';

select * from student where stuname like '林%'

select * from student where stuname like '%丽%'

select * from student where stuname like '%丽'

select * from student where stuname like '_丽%'

select * from student where stuname like '__丽%'

select * from student limit [m,]n

select * from student limit 5;

select * from student limit 4,6;。

select distinct ClassNo from student;

select stuno as '学号', stuname as '姓名',pwd from student

select * from student order by stuname asc

select * from student order by stuname desc

select * from student order by classno asc, stuname desc

select * from Student

SELECT STUNAME, PWD FROM STUDENT

SELECT STUNAME, stuno FROM STUDENT

select * from student where classno='20000001'

select stuno,stuname from student where classno='20000001‘

如：select classname,stuno,stuname from class,student where class.ClassNo=student.ClassNo;

select 字段列表2 from 表2 where 条件。

Select 字段列表 from 左表, 右表 where 条件

select A,B,C from l,r where a>1 and c='8';

或：select classname,stuname,pwd from student, class where student.classno=class.ClassNo;

select * from student where ClassNo=(select classno from class where ClassName='00电子商务');

Left(字符串, n)

Select left('青岛港湾职业技术学院',2)

right(字符串, n)

Select right('青岛港湾职业技术学院',2)

Substring(字符串，m,n) 或写成substr(字符串，m,n)

Select substring('青岛港湾职业技术学院',3,2)

Replace(字符串1,字符串2,字符串3)

Lower()

Select lower('ABCD') abcd

Upper()

select UPPER('abcd') ABCD

select concat(CAST(123 AS char),'456');

Concat(‘字符串1’,’字符串2’)

select concat('123','456');

select HOUR(now());

select MINUTE(now());

select SECOND(now());

select datediff(now(),'2023-10-1');

12、DATE_ADD(日期1,INTERVAL n 类型);

select DATE_ADD(now(),INTERVAL 2 year);

select DATE_sub(now(),INTERVAL 2 year);

select SQRT(3)

select round(10.3454,2)

select round(10.3445,2)

select 10*rand() 0到10之间，不包括0和10 0.0001-----9.9999

select ceiling(10*rand()) 1到10的整数，包括1和10

select floor(10*rand()) 0到9的整数，包括0和9

select ceiling(52*rand()) 1---52 类似：抽大奖，抽号码