MySQL数据库基本语句展示

-- 双中划线+空格：注释(单行注释)，也可以使用#号

-- 创建

create database mydb charset utf8;

-- 创建关键字数据库

create database database charset utf8;

-- 使用反引号

create database `database` charset utf8;

-- 创建中文数据库

create database 中国 charset utf8;

create database `中国` charset utf8;

-- 解决方案：告诉服务器当前中文的字符集是什么

set names gbk;

-- 连接本机数据库：

mysql -h localhost -p 3306 -u root -p

mysql -uroot -p

-- 退出：

exit

\q

quit

-- 查看所有的数据库：

show databases;

-- 创建数据库

create database informationtest charset utf8;

-- 查看以information_开头的数据库(_需要被转义)

show databases like 'information\_%';

show databases like 'information_%'; -- 相当于information%

-- 查看数据库的创建语句

show create database mydb;

show create database `database`; -- 关键字需要使用反引号

-- 修改数据库informationtest的字符集

alter database informationtest charset gbk;

-- 删除数据库

drop database 数据库名字;

-- 创建表

-- 显式地将student表放到mydb数据库下

create table if not exists mydb.student(

name varchar(10),

gender varchar(10),

number varchar(10),

age int

)charset utf8;

-- 创建数据表

-- 进入数据库

use mydb;

-- 创建表

create table class(

name varchar(10),

room varchar(10)

)charset utf8;

-- 查看所有表

show tables;

-- 查看以s结尾的表

show tables like '%s';

-- 查看表创建语句

show create table student\g; -- \g等价于 ;

show create table student\g; -- 将查到的结构旋转90度变成纵向

-- 查看表的结构(表中的字段信息)：

desc class;

describe class;

show columns from class;

-- 重命名表:student -> my_student

rename table student to my_student;

-- 修改表选项：字符集

alter table my_student charset=gbk;

-- 给学生表增加id字段，放到第一个位置(用first来表示)

alter table my_student add column id int first;

-- 将学生表中的number学号字段变成固定长度，且放到第二位(id之后)

alter table my_student modify number char(10) after id;

-- 修改学生表中的gender字段为sex

alter table my_student change gender sex varchar(10);

-- 删除学生表中的age年龄字段

alter table my_student drop age;

-- 删除数据表

drop table 表名1，表名2....;

-- 给全表字段插入数据

insert into my_student values

(1,'bc20170001','jim','male'),

(2,'bc20170002','lily','female');

-- 给部分字段插入数据(指定字段列表)

insert into my_student(number,sex,name,id) values

('bc20170003','male','tom',3),

('bc20170004','female','lucy',4);

-- 查看所有数据

select * from my_student;

-- 查看指定字段、条件的数据

-- 查看满足id为1的学生信息

select id,number,sex,name from my_student where id=1;

-- 更新数据

update my_student set sex = 'female' where name='jeson';

-- 删除数据

delete from my_student where name = '';

-- 插入数据(带中文的)

insert into my_student values

(5,'bc20170005','小马','男');

-- 查看所有字符集

show character set;

-- 查看服务器默认的对外处理的字符集

show variables like 'character_set%';

-- 修改服务器认为的客户端数据的字符集为gbk

set character_set_client=gbk;

-- 修改服务器给定数据的字符集为gbk

set character_set_results=gbk;

-- 快捷设置字符集

set names gbk;

-- 查看所有校对集

show collation;

-- 创建表使用不同的校对集

create table my_collate_bin(

name char(1)

)charset utf8 collate utf8_bin;

create table my_collate_ci(

name char(1)

)charset utf8 collate utf8_general_ci;

-- 插入数据

insert into my_collate_bin values

('a'),('a'),('b'),('b');

insert into my_collate_ci values

('a'),('a'),('b'),('b');

-- 排序查找

select * from my_collate_bin order by name;

select * from my_collate_ci order by name;

-- 有数据后修改校对集

alter table my_collate_ci collate=utf8_bin;

alter table my_collate_ci collate=utf8_general_ci;

-- 创建整型表

create table my_int(

int_1 tinyint,

int_2 smallint,

int_3 int,

int_4 bigint

)charset utf8;

-- 插入数据

insert into my_int values(100,100,100,100); -- 有效数据

insert into my_int values('a','b','199','f'); -- 无效数据，类型限定

insert into my_int values(255,10000,100000,1000000); -- 错误：超出范围

-- 给表增加一个无符号类型

alter table my_int add int_5 tinyint unsigned; -- 无符号类型

-- 插入数据

insert into my_int values(127,10000,100000,1000000,255);

-- 指定显示宽度为1

alter table my_int add int_6 tinyint(1) unsigned;

-- 插入数据

insert into my_int values(127,0,0,0,255,255);

-- 显示宽度为2,0填充

alter table my_int add int_7 tinyint(2) zerofill;

-- 插入数据

insert into my_int values(1,1,1,1,1,1,1);

insert into my_int values(100,100,100,100,100,100,100);

-- 浮点数表

create table my_float(

f1 float,

f2 float(10,2), -- 10位在精度范围之外

f3 float(6,2) -- 6位在精度范围之内

)charset utf8;

-- 插入数据

insert into my_float values(1000.10,1000.10,1000.10);

insert into my_float values(1234567890,12345678.90,1234.56);

-- 3*10^38 3.01*10^7

insert into my_float values(3e38,3.01e7,1234.56);

-- 后两个是最大值

insert into my_float values(9999999999,99999999.99,1000.10);

-- 超出长度插入数据

-- 小数部分可以超出长度

insert into my_float values(123456,1234.12345678,123.9876543);

-- 最后一个整数部分超出

insert into my_float values(123456,1234.12,12345.56);

-- 创建定点数表

create table my_decimal(

fl float(10,2),

d1 decimal(10,2)

)charset utf8;

-- 插入数据

insert into my_decimal values(12345678.90,12345678.90); -- 有效数据

insert into my_decimal values(1234.123456,1234.123456); -- 小数部分可以超出

-- 查看警告

show warnings;

-- 插入数据

insert into my_decimal values(99999999.99,99999999.99); -- 没有问题

insert into my_decimal values(99999999.99,99999999.999); -- 进位超出范围

-- 创建时间日期表

create table my_date(

d1 datetime,

d2 date,

d3 time,

d4 timestamp,

d5 year

)charset utf8;

-- 插入数据

insert into my_date values(

'2017-10-26 10:30:36',

'2017-10-26',

'10:30:36',

'2017-10-26 10:30:36',

2017

);

-- 时间使用负数

insert into my_date values(

'2017-10-26 10:30:36',

'2017-10-26',

'-210:30:36', -- 表示从过去到现在的时间段

'2017-10-26 10:30:36',

2017

);

-- year可以使用2位或者4位

insert into my_date values(

'2017-10-26 10:30:36',

'2017-10-26',

'-2 10:30:36', -- -2表示过去2天，即48小时，48+10 ->-58

'2017-10-26 10:30:36',

2017

);

insert into my_date values(

'2017-10-26 10:30:36',

'2017-10-26',

'-58:30:36', -- -2表示过去2天，即48小时，48+10 ->-58

'2017-10-26 10:30:36',

2017

);

insert into my_date values(

'2017-10-26 10:30:36',

'2017-10-26',

'10:30:36', -- -2表示过去2天，即48小时，48+10 ->-58

'2017-10-26 10:30:36',

69

);

insert into my_date values(

'2017-10-26 10:30:36',

'2017-10-26',

'10:30:36', -- -2表示过去2天，即48小时，48+10 ->-58

'2017-10-26 10:30:36',

70

);

-- timestamp:修改记录

update my_date set d1='2017-10-26 10:43:45' where d5=2069;

-- 查看时间戳

select unix_timestamp();

-- 创建枚举表

create table my_enum(

gender enum('男','女','保密')

)charset utf8;

-- 插入数据

insert into my_enum values('男'),('保密'); -- 有效数据

insert into my_enum values('male'); -- 错误数据，没有该元素

-- 将字段结果取出来进行+0运算

select gender+0,gender from my_enum;

-- 数值插入枚举元素

insert into my_enum values(1),(2);

-- 创建集合表

create table my_set(

hobby set('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')

)charset utf8;

-- 插入数据

insert into my_set values('足球,台球,网球');

insert into my_set values(3);

-- 查看集合的数据

select hobby+0,hobby from my_set;

-- 98转成二进制64+32+2=01100010

-- 数值插入集合元素

insert into my_set values(255);

-- 颠倒元素出现的顺序

insert into my_set values('网球,台球,足球'); -- 还是98

-- 求出varchar在utf8和gbk下的实际最大值

create table my_utf8(

name varchar(21844) -- 21844*3+2=65532+2=65534

)charset utf8;

create table my_gbk(

name varchar(32766) -- 32766*2+2=65532+2=65534

)charset gbk;

create table my_utf81(

age tinyint,

name varchar(21844)

)charset utf8;

create table my_gbk1(

age tinyint,

name varchar(32766)

)charset gbk;

-- 释放null

create table my_utf82(

age tinyint not null,

name varchar(21844) not null

)charset utf8;

create table my_gbk1(

age tinyint not null,

name varchar(32766) not null

)charset gbk;

-- text占用十个字节长度

create table my_text(

name varchar(21841) not null,

content text not null

)charset utf8;

-- 创建班级表

create table my_class(

name varchar(20) not null,

room varchar(20) null -- 代表允许为空，不写默认就是允许为空

)charset utf8;

-- 创建表

create table my_teacher(

name varchar(20) not null comment '姓名',

money decimal(10,2) not null comment '工资'

)charset utf8;

-- 默认值

create table my_default(

name varchar(20) not null,

age tinyint unsigned default 0,

gender enum('男','女','保密') default '男'

)charset utf8;

-- 插入数据

insert into my_default(name) values('阿飞');

insert into my_default values('男闺蜜',18,default);

-- 增加主键

create table my_pri1(

name varchar(20) not null comment '姓名',

number char(10) primary key comment '学号:bc2017+0001,不能重复'

)charset utf8;

-- 复合主键

create table my_pri2(

number char(10) comment '学号：bc2017+0001',

course char(10) comment '课程代码：bc2589+0001',

score tinyint unsigned default 60 comment '成绩',

-- 增加主键限制：学号和课程号应该是对应的，具有唯一性

primary key(number,course)

)charset utf8;

-- 追加主键

create table my_pri3(

course char(10) not null comment '课程编号：bc2589+0001',

name varchar(10) not null comment '课程名称'

)charset utf8;

alter table my_pri3 modify course char(10) primary key comment '课程编号：bc2589+0001';

alter table my_pri3 add primary key(course);

-- 向pril,2表插入数据

insert into my_pri1 values

('古天乐','bc20170001'),

('蔡康永','bc20170002');

insert into my_pri2 values

('bc20170001','bc25890001',90),

('bc20170001','bc25890002',85),

('bc20170002','bc25890001',92);

-- 主键冲突(重复)

insert into my_pri1 values

('刘涛','bc20170002'); -- 不可以：主键冲突

insert into my_pri2 values

('bc20170001','bc25890001',100); -- 不可以,冲突

-- 删除主键

alter table my_pri3 drop primary key;

-- 自增长

create table my_auto(

id int primary key auto_increment comment '自动增长',

name varchar(10) not null

)charset utf8;

-- 触发自增长

insert into my_auto(name) values('邓丽君');

insert into my_auto values(null,'成龙');

insert into my_auto values(default,'吴绮莉');

-- 指定数据

insert into my_auto values(6,'黄晓明');

insert into my_auto values(null,'杨颖');

-- 修改表选项的值

alter table my_auto auto_increment=4; -- 向下修改(大小)，不生效

alter table my_auto auto_increment=10;

-- 查看自增长变量

show variables like 'auto_increment%';

-- 修改自增长变量

set auto_increment_increment=5; -- 一次自增5

-- 插入记录：使用自增长

insert into my_auto values(null,'杨紫');

insert into my_auto values(null,'张一山');

-- 删除自增长

alter table my_auto modify id int primary key; -- 错误：主键理论上是单独存在的

-- 有主键的时候，千万不要再加主键

alter table my_auto modify id int;

-- 唯一键

create table my_unique1(

number char(10) unique comment '学号：唯一，运行为空',

name varchar(20) not null

)charset utf8;

create table my_unique2(

number char(10) not null comment'学号',

name varchar(20) not null,

-- 增加唯一键

unique key(number)

)charset utf8;

create table my_unique3(

id int primary key auto_increment,

number char(10) not null,

name varchar(20) not null

)charset utf8;

-- 追加唯一键

alter table my_unique3 add unique key(number);

-- 插入数据

insert into my_unique1 values

(null,'大雄'),

('bc20070001','胖虎'),

(null,'小静');

--唯一键冲突

insert into my_unique1 values

('bc20070001','哆啦a梦');

-- 删除唯一键

alter table my_unique3 drop index number;

-- 给my_class表增加一个主键

alter table my_class add primary key(name);

-- 插入数据

insert into my_class values('java1707b','a204');

insert into my_class values('java1707b','b510');

insert into my_class values('java1708','a203');

-- 主键冲突：更新

insert into my_class values('java1707b','b510')

-- 冲突处理

on duplicate key update

-- 更新教室

room='b510';

-- 主键冲突：替换

replace into my_class values('java1708','b409');

replace into my_class values('java1710','c110');

-- 复制创建表

create table my_copy like my_gbk;

-- 蠕虫复制：先查出数据,然后将查出的数据新增一遍

insert into my_copy select * from my_collate_bin;

insert into my_copy select * from my_copy;

-- 更新部分a变成c

update my_copy set name='c' where name='a' limit 3;

-- 删除数据:限制记录数为10

delete from my_copy where name='b' limit 10;

-- 给学生表增加主键

alter table my_student modify id int primary key auto_increment;

-- 清空表,重置自增长

truncate my_student;

-- select选项

select my_copy;

select all = from by_copy;

-- 去重

select distinct * from my_copy;

-- 向学生表插入数据

insert into my_student values

(null,'bc20170001','张三','男'),

(null,'bc20170002','李四','男'),

(null,'bc20170003','王五','女'),

(null,'bc20170004','赵六','男'),

(null,'bc20170005','小明','男');

-- 字段别名

select

id,

number as 学号,

name as 姓名,

sex 性别 from my_student;

-- 多表数据源

select * from my_student,my_class;

-- 子查询

select * from (select * from my_student) as s;

-- 增加age年龄和height身高字段

alter table my_student add age tinyint unsigned;

alter table my_student add height tinyint unsigned;

-- 增加字段值：rand取得一个0-1之间的随机数，floor向下取整

update my_student set

age=floor(rand()*20+20), -- 年龄在20-40

height=floor(rand()*20+170); -- 身高在170-190之间

-- where字句：返回结果0或1,0代表false，1代表true

-- 判断条件：比较运算符：>,<,>=,<=,<>,=,like,between,and,in/not in

-- 逻辑运算符：&&(and)、||(or)、!(not)

-- 找出学生id为1、3、5的学生

select * from my_student where id=1||id=3||id=5; -- 逻辑判断

select * from my_student where id in(1,3,5); -- 在集合中

-- 找身高在175到180之间的学生

select * from my_student where height>=175 and height<=180;

select * from my_student where height between 175 and 180;

select * from my_student where height between 180 and 175;

select * from my_student where 1;

-- group by

-- 基本语法：group by 字段名[asc|desc]

-- 根据性别分组

select * from my_student group by sex;

-- 分组统计：身高高矮、平局年龄、总年龄

select sex,count(*),max(height),min(height),avg(age),sum(age)

from my_student

group by sex;

select sex,count(*),count(age),min(height),avg(age),sum(age)

from my_student

group by sex;

select sex,count(*),count(age),min(height),avg(age),sum(age)

from my_student

group by sex desc;

-- 修改id为4的记录，把年龄设置为null

update my_student set age=null where id=4;

-- 修改id为1的记录，把性别设置为女

update my_student set sex='女' where id=1;

-- 给班级表加主键

alter table my_class add id int primary key auto_increment;

alter table my_class drop primary key;

alter table my_student add c_id int;

update my_student set c_id=ceil(rand()*3);

-- 多字段分组：先班级，后男女

insert into my_student values(6,'bc20170006','小芳','女',18,160,2);

update my_student set c_id=2 where id=5;

select c_id,sex,count(*) from my_student group by c_id,sex; -- 多字段排序

select c_id,sex,count(*),group_concat(name)

from my_student group by c_id,sex;

-- 统计

select c_id,count(*) from my_student group by c_id;

-- 回溯统计

select c_id,count(*) from my_student group by c_id with rollup;

-- 多字段分组回溯统计

select c_id,sex,count(*),group_concat(name)

from my_student group by c_id,sex;

select c_id,sex,count(*),group_concat(name)

from my_student group by c_id,sex with rollup;

-- 求出所有班级人数大于等于2的学生人数

select c_id,count(*) from my_student

group by c_id having count(*)>=2;

select c_id,count(*) as total from my_student

group by c_id having total>=2;

-- having子句进行条件查询

select name as 名字,number as 学号 from my_student

having 名字 like '张%';

select name as 名字,number as 学号 from my_student

where 名字 like '张%';

-- 排序

select * from my_student group by c_id;

select * from my_student order by c_id;

-- 多字段排序：先班级排序，后性别排序

select * from my_student order by c_id,sex desc;

-- 查询学生：前两个

select * from my_student limit 2;

select * from my_student limit 0,2; -- 记录数是从0开始编号

select * from my_student limit 2,2;

select * from my_student limit 5,2;

-- 更改id为班级表的第一列

alter table my_class change id id int first;

-- 交叉连接 my_student cross join my_class是数据源

select * from my_student cross join my_class;

-- 内连接

select * from my_student inner join my_class

on my_student.c_id=my_class.id;

select * from my_student inner join my_class

on c_id=my_class.id;

select * from my_student inner join my_class

on c_id=id; -- 两张表都有id字段

select s.*,c.name as c_name,c.room -- 字段别名

from my_student as s inner join my_class as c -- 表别名

on s.c_id=c.id;

-- where代替on

select s.*,c.name as c_name,c.room -- 字段别名

from my_student as s inner join my_class as c -- 表别名

where s.c_id=c.id;

-- 把学生表id为5的记录的c_id设置为null

update my_student set c_id=null where id=5;

-- 左链接

select s.*,c.name as c_name,c.room -- 字段别名

-- 左表为主要，最终记录数至少不少于左表已有的记录数

from my_student as s left join my_class as c -- 表别名

on s.c_id=c.id;

-- 右链接

select s.*,c.name as c_name,c.room -- 字段别名

-- 右表为主要，最终记录数至少不少于右表已有的记录数

from my_student as s right join my_class as c -- 表别名

on s.c_id=c.id;

-- 左连接与右连接互转

select s.*,name as c_name,c.room

from my_class as c right join my_student as s

on s.c_id=id;

-- 自然连接

select * from my_student natural join my_class;

-- 修改班级表的name字段名为c_name

alter table my_class change name c_name varchar(20) not null;

-- 自然左外连接

select * from my_student natural left join my_class;

-- 外连接模拟自然外连接：using

select * from my_student left join my_class using(id);

-- 外键约束

create table my_foreign1(

id int primary key auto_increment,

name varchar(20) not null comment '学生姓名',

c_id int comment '班级id', -- 普通字段

-- 增加外键

foreign key(c_id) references my_class(id)

)charset utf8;

-- 外键约束

create table my_foreign2(

id int primary key auto_increment,

name varchar(20) not null comment '学生姓名',

c_id int comment '班级id' -- 普通字段

)charset utf8;

-- 增加外键

alter table my_foreign2 add

-- 指定外键名

constraint student_class_1

-- 指定外键字段

foreign key(c_id)

-- 引用父表主键

references my_class(id);

-- 删除外键

alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;

-- 插入数据，外键字段在父表中不存在

insert into my_foreign2 values(null,'郭富城',4); -- 没有4班级

insert into my_foreign2 values(null,'项羽',1);

insert into my_foreign2 values(null,'刘邦',2);

insert into my_foreign2 values(null,'韩信',2);

-- 更新父表的记录

update my_class set id=4 where id=1; -- 失败：id=1的记录已经被学生引用

update my_class set id=4 where id=3; -- 可以：没有引用

-- 插入数据

insert into my_foreign1 values(null,'马超',3);

-- 增加外键

alter table my_foreign1 add foreign key(c_id) references my_class(id); -- 失败

-- 创建外键：指定模式：删除置空，更新级联

create table my_foreign3(

id int primary key auto_increment,

name varchar(20) not null,

c_id int,

-- 增加外键

foreign key(c_id)

-- 引用父表

references my_class(id)

-- 指定删除模式

on delete set null

-- 指定更新模式

on update cascade

)charset utf8;

-- 插入数据

insert into my_foreign3 values

(null,'刘备',1),

(null,'曹操',1),

(null,'孙权',1),

(null,'诸葛亮',2),

(null,'周瑜',2);

-- 解除my_foreign2表的外键

alter table my_foreign2 drop foreign key student_class_1;

-- 更新父表的主键

update my_class set id=3 where id=1;

-- 删除父表的主键

delete from my_class where id=2;

-- 联合查询

select * from my_class

union -- 默认去重(重复)

select * from my_class;

select * from my_class

union all -- 不去重(重复)

select * from my_class;

select id,c_name,room from my_class

union all -- 不去重(重复)

select name,number,id from my_student;

-- 需求：男按身高升序，女降序

(select * from my_student where sex='男'

order by height asc limit 9999999)

union

(select * from my_student where sex='女'

order by height desc limit 9999999);

-- 标量子查询

select * from my_student where c_id=

-- id一定只有一个值(一行一列)

(select id from my_class where c_name='java1707b');

-- 列子查询

select * from my_student where c_id in

(select id from my_class);

-- any,some,all

select * from my_student where c_id=any

(select id from my_class);

select * from my_student where c_id=some

(select id from my_class);

select * from my_student where c_id=all

(select id from my_class);

-- 所有结果(null除外)

select * from my_student where c_id=any

(select id from my_class);

-- 所有结果(null除外)

select * from my_student where c_id=some

(select id from my_class);

-- 2(null除外)

select * from my_student where c_id=all

(select id from my_class);

insert into my_class values(1,'java1708','a203');

select * from my_student where

age=(select max(age) from my_student)

and

height=(select max(height) from my_student);

-- 行子查询

select * from my_student where (age,height)= -- (age,height)叫行元素

(select max(age),max(height) from my_student);

select * from my_student order by age desc,height desc limit 1;

select * from my_student order by height desc;

select * from my_student group by c_id order by height desc; -- 每个班选出第一个学生

-- 表子查询

select * from

(select * from my_student order by c_id order by height desc) as student

group by c_id order by height desc;

select exists(select * from my_student where id=100);

-- exists子查询

select * from my_student where

exists(select * from my_class where id=1); -- 是否成立

-- exists子查询

select * from my_student where

exists(select * from my_class where id=2);

-- 视图：单标+多表

create view my_v1 as

select * from my_student;

create view my_v2 as

select * from my_class;

create view my_v3 as

select * from my_student as s

left join my_class as c on s.c_id=c_id; -- id 重复

-- 多表视图

create view my_v3 as

select s.*,c.c_name,c.room from my_student as s

left join my_class as c on s.c_id=c_id;

-- 查看视图创建语句

show create view my_v3\g

-- 视图的使用

select * from my_v1;

select * from my_v2;

select * from my_v3;

-- 修改视图

alter view my_v1 as

select id,name,age,height,c_id from my_student;

-- 删除视图

drop view my_v4;

-- 多表视图不能插入数据

insert into my_v3 values

(null,'bc20170007','张三丰','男',150,180,1,'python1711','a208');

-- 将学生的学号字段设置成不允许为空

alter table my_student modify number char(10) not null unique;

-- 单表视图插入数据，视图不包括所有不允许为空的字段

insert into my_v1 values

(null,'张三丰',150,'男',180,1);

-- 单表视图插入数据

insert into my_v2 values(2,'python0711','a204');

--多表视图删除数据

delete from my_v3 where id=1;

-- 单表视图删除数据

delete from my_v2 where id=4;

-- 多表视图更新数据

update my_v3 set c_id=3 where id=5;

-- 视图：age字段限制更新

create view my_v4 as

select * from my_student where age>30 with check option;

-- 表示视图的数据来源都是年龄大于30岁的，是由where age>30决定的

-- with check option决定通过视图更新的时候

-- 不能将已经得到的数据age>30的改成30的

-- 将视图可以查到的数据，改成年龄小于30

update my_v4 set age=28 where id=3;

-- 可以修改数据让视图可以查到，可以改，但是无效果

update my_v4 set age=32 where id=3;

-- 获取所有班级中最高的一个学生

create view my_v5 as

select * from my_student order by height desc;

select * from my_v5 group by c_id;

select * from my_student group by c_id order by height desc;

-- 指定这个算法为临时表

create algorithm=temptable view my_v6 as

select * from my_student order by height desc;

select * from my_v6 group by c_id;

-- 创建myisam表

create table my_myisam(

id int

)charset utf8 engine=myisam;

-- 向my_myisam表插入几条记录

insert into my_myisam values(1),(2),(3);

-- 单表的数据备份

select * into outfile 'd:\流\student.txt' from my_student;

select * into outfile 'd:\流\class.txt' from my_class;

-- 指定备份处理方式

select * into outfile

'd:\流\class1.txt'

-- 字段处理

fields

enclosed by '"' -- 数据使用双引号包裹

terminated by '|' -- 使用竖线分割字段数据

-- 行处理

lines

starting by 'start:' -- 每行以start:开始

from my_class;

-- 删除数据

delete from my_class;

-- 还原数据

load data infile

'd:\流\class1.txt'

into table my_class

-- 字段处理

fields

enclosed by '"' -- 数据使用双引号包裹

terminated by '|' -- 使用竖线分割字段数据

-- 行处理

lines

starting by 'start:'; -- 每行以start:开始

-- sql备份

mysqldump -uroot -p20000816 mydb my_student > d:\流\student.sql

-- 整库备份

mysqldump -uroot -p20000816 mydb > d:\流\mydb.sql

-- 还原数据，mysql客户端还原

mysql -uroot -p20000816 mydb < d:\流\student1.sql

-- sql指令还原sql备份

source d:\666\student.sql

-- 创建一个账户表

create table my_account(

id int primary key auto_increment,

number char(16) not null unique comment '账户',

name varchar(20) not null,

money decimal(10,2) default 0.0 comment '账户余额'

)charset utf8;

-- 插入数据

insert into my_account values

(null,'0000000000000001','张三',1000),

(null,'0000000000000002','李四',2000);

-- 张三转账给1000元给李四

update my_account set money=money-1000 where id=1;

-- 事务安全

-- 开启事务

start transaction;

-- 事务操作：1.李四账户减少

update my_account set money=money-1000 where id=2;

-- 事务操作：2.张三账户增加

update my_account set money=money+1000 where id=1;

-- 提交事务

commit;

-- 回滚点操作

-- 开启事务

start transaction;

-- 事务处理1：张三发工资了，加钱

update my_account set money=money+10000 where id=1;

-- 设置回滚点

savepoint sp1;

-- 银行扣税

update my_account set money=money-10000*0.05 where id=2; -- 错误

-- 回滚到回滚点

rollback to sp1;

-- 继续操作

update my_account set money=money-10000*0.05 where id=1;

-- 查看结果

select * from my_account;

-- 提交结果

commit;

-- 显示变量autocommit(模糊查询)

show variables like 'autocommit';

-- 关闭事务自动提交

set autocommit=0;

-- 开启事务自动提交

set autocommit=1;

-- 锁机制

start transaction;

update my_account set money=money+500 where name='张三';

update my_account set money=money+1000 where id=2;

-- 查看所有的系统变量

show variables;

-- 系统变量值

select @@version,@@autocommit,@@auto_increment_offset,

@@character_set_results;

-- 修改会话级别变量

set autocommit=0;

-- 修改全局级别变量

set global autocommit=0;

-- 定义自定义变量

set @name='张三';

-- 查看变量

select @name;

-- 定义变量

set @age:=18;

-- 从表中获取数据赋值给变量

select @name:=name,name from my_student;

select name,age from my_student where id=2 into @name,@age;

-- 创建表

create table my_goods(

id int primary key auto_increment,

name varchar(20) not null,

price decimal(10,2) default 1,

inv int comment '库存数量'

)charset utf8;

insert into my_goods values

(null,'iphone8',5888,100),

(null,'mate10保时捷',10900,100);

create table my_order(

id int primary key auto_increment,

g_id int not null comment '商品id',

g_number int comment '商品数量'

)charset utf8;

-- 触发器：订单生成一个，商品库存减少

-- 临时修改语句结束符

delimiter $$

create trigger after_order after insert on my_order for each row

begin

-- 触发器内容开始

update my_goods set inv=inv-1 where id=2;

end

-- 结束触发器

$$

-- 修改临时语句结束符

delimiter ;

-- 查看所有触发器

show triggers;

-- 查看触发器创建语句

show create trigger after_order\g

select * from information_schema.triggers\g

-- 插入订单

insert into my_order values(null,1,2);

-- 删除触发器

drop trigger after_order;

-- 触发器：订单生成一个，商品库存减少

-- 临时修改语句结束符

delimiter $$

create trigger after_order after insert on my_order for each row

begin

-- 触发器内容开始,old没有,new代表新的订单记录

update my_goods set inv=inv-new.g_number where id=new.g_id;

end

-- 结束触发器

$$

-- 修改临时语句结束符

delimiter ;