MySQL知识点：

-- 连接认证

mysql.exe -h localhost -P 3306 -u root -p

mysql -u root -p

-h 找到电脑里的主机地址

-P 大P-默认端口3306

-p 小p-密码

-u user名字

show databases; -- 查看所有数据库

-- 退出命令

exit,quit,\q

-- 创建数据库

create database mydatabase charset utf8;

-- 创建关键字数据库

create databasedatabasecharset utf8;

-- 告诉服务器当前中文的字符集是什么

set names gbk;

-- 创建中文数据库

create database 中国 charset utf8;

-- 创建数据库

create database informationtest charset utf8;

-- 查看以information_开始的数据库  (匹配需要被转义)

show databases like 'information_%';

show databases like 'information

%';  --相当于information%

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

show create  database mydatabase;

show create  database 'database';

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

alter database informationtest charset GBK;

-- 删除数据库    -- 数据库名字

drop database informationtest;

-- 创建表

create  table if not exists mydatabase.student (

-- 显示的将student表放到mydatabase数据库下面

name varchar(10),

gender varchar (10),

number varchar (10),

age int

)charset utf8;

-- 创建数据表  --常用的

-- 进入数据库

use mydatabase;

--创建表

create table class (

name varchar (10),

room varchar (10)

) charset utf8;

-- 查看所有表

show tables;

-- 查看以s结尾的表

show tables like '%s';

-- 查看表的创建语句：

show create table student;

show create table student\g    -- \g 相当于 ;

show create table student\G    -- \G 将查到的结构旋转90度变成纵向---------"方便观察"

-- 查看表结构

desc class;

describe class;

show columns from class;

-- 重命名表（student表 -> tal_student）

rename table student to my_student;

-- 修改表选项：字符集

alter table my_student charset = GBK;

-- 查看创建表的语句

show create table my_student;

-- 查看表结构

desc my_student;

-- 给学生表增加ID， first---放到第一个位置  column 列字段的意思

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);

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

alter table my_student drop age;

-- 删除数据表

drop table class;

-- 插入数据

insert into my_student values

(1,'bc20190001','jim','male'),

(2,'bc20190002','Lily','female');

-- 插入数据：指定字段列表

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

('ba20190003','male','Tom',3),

('ba20190004','female','LUcy',4);

-- 查看所有数据

select * from my_student;

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

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

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

-- 更新数据                        where条件 先开始的表名

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

-- 删除数据

delete from my_student where sex='male';

****************************  第二天的  *******************************

-- 创建整型表

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,100000,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;  -- zerofill; 自动变成 unsigned无符号

-- 插入数据

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位在精度范围之内

);

-- 插入数据

insert  into my_float

values(1000.10,1000.10,1000.10);

insert  into my_float

values(1234567890,12345678.90,123.56);

insert  into my_float

values(3e38,3.01e7,1234.56);

insert  into my_float

values(9999999999,99999999.99,9999.99); -- 后两个是最大值

-- 超出长度插入数据

insert into my_float

values (123456,1234.12345678,123.9876543);  -- 小数部分可以超出长度

insert into my_float

values (132456,1324.12,123456.56); -- 最后一个整数部分超出

-- 创建定点数表

create  table my_decimal(

f1 float (10,2),

d1 decimal (10,2)

);

-- 插入数据

insert into my_decimal

values (12345678.90,12346578.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

);

-- 插入数据

insert into my_date

values ('2019-11-21 13:38:36',

'2019-11-21','13:38:36',

'2019-11-21 13:38:36','2019');

-- 时间使用负数

insert into my_date

values ('2019-11-21 13:38:36',

'2019-11-21','-13:38:36',

'2019-11-21 13:38:36','2019');

insert into my_date

values ('2019-11-21 13:38:36',

'2019-11-21','-213:38:36',

'2019-11-21 13:38:36','2019');

insert into my_date

values ('2019-11-21 13:38:36',

'2019-11-21','-2:38:36',

'2019-11-21 13:38:36','2019'); --  -2表示过去2天，就是48小时  -- year可以使用2位或者4位

insert into my_date

values ('2019-11-21 13:38:36',

'2019-11-21','13:38:36',

'2019-11-21 13:38:36','69');

insert into my_date

values ('2019-11-21 13:38:36',

'2019-11-21','13:38:36',

'2019-11-21 13:38:36','70');

-- timestamp；修改记录

update my_date set d1=

'2019-11-21 13:46:45' where d5=2069;

-- 创建枚举表

create table my_enum(

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

);

-- 插入数据

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_class(

name varchar (20) not null,

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

);

-- 创建表

create table my_teacher(

name varchar (20) not null comment

'姓名',

money decimal (10,2) not null

comment '工资'

);

-- 默认值

create table my_default(

name varchar (20) not null,

age tinyint unsigned default 0,

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

default '男'

);

-- 插入数据

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 '学号：bc2019+0001，不能重复'

);

-- 复合主键

create table my_pri2(

number char (10) comment

'学号: bc20190001',

course char (10) comment

'课程代码：bc25890001',

score tinyint unsigned default 60

comment '成绩',

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

primary key(number, course)

);

-- 追加主键

create table my_pri3(

course char (10) not null comment

'课程代码：bc25890001',

name varchar (10) not null comment

'课程名字'

);

-- 第一种方式

alter table my_pri3 modify course

char(10)  primary key comment

'课程代码:bc25890001';

-- 把第一种用drop table my_pri3; 删除然后在创建一个在用第二种方式

-- 删除表

drop table my_pri3;

-- 第二种方式

alter table my_pri3 add primary

key(course);

alter table my_pri3 add primary

key(course);  -- 也可以是复合主键

***************************  第三天  *****************************

-- 向pri1、2表插入数据

insert into my_pri1

values ('古天乐','bc20190001'),

('臭蛋','bc20190002');

insert into my_pri2

values('bc20190001','bc25890001',90),

('bc2019002','bc25890002',85),

('bc2019003','bc25890003',92);

-- 主键冲突（重复）

insert into my_pri1

values('刘涛','bc20190002'); -- 不可以；主键冲突

insert into my_pri2

values('bc20190001','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);-- 给number增加唯一键

alter table my_unique3 add unique

key(name);-- 给name增加唯一键

-- 插入数据

insert into my_unique1

values (null, '大熊'),

('bc20190001','胖虎'),

(null,'静香');

insert into my_unique1

values ('bc20190001','哆啦A梦');

-- 删除唯一键  -----删除的是索引  index

alter table my_unique3 drop index number;

-- 自关联  自关联就是看房山区的父id是哪个，是1 北京就是父id

-- id    name    pid

-- 1      北京    null

-- 2      朝阳区    1

-- 3      房山区    1

-- 4      吉林省    null

-- 5      长春      4

-- 6      吉林市    4

-- 空间换时间

-- 商品表

-- id    name    pic

-- 商品样式表

-- id  pid style  pic

****************************  第四天  *************************

-- 给班级表增加主键

alter table my_class add primary

key(name);

-- 插入数据

insert into my_class values (

'python1907','B408');

insert into my_class values (

'python1907','B407'); -- 错误；主键冲突

insert into my_class values (

'python1907','B407')

-- 冲突处理

on duplicate key update

-- 更新教室

room='B407';

insert into my_class values (

'python1903','B408');

-- 主键冲突；替换

replace into my_class values (

'python1903','B406');

replace into my_class values (

'python1910','B409');

-- 复制创建表,

create table my_copy like my_class;

-- 删除主键

alter table my_copy drop primary key;

-- 在MySQL中去掉一个表的主键需要分2中的情况；

-- 1；该列（column）不光设置为主键（primary key），

-- 还有自增长（auto_increment）；

-- 句式：alter table +表名+ modify id int, drop primary key;

-- 2、如果没有设置为自增长（auto_increment），

-- 那么可以直接删除主键（primary key ）;

-- 句式：alter table +表名+drop primary key;

-- 蠕虫复制-------用来测试表的压力以及效率

insert into my_copy select * from my_class;

insert into my_copy select * from my_copy;

-- 更新部分B406变成A406

update my_copy set room='A408' where room='B408' limit 3;

-- 删除数据；限制记录数为5

delete from my_copy where room='B409' limit 5;

-- 给学生表增加主键

alter table my_student modify id int primary key auto_increment;

-- 清空表，重置自增长

truncate my_student;

-- 以下三句有什么区别？

delete from 表名; -- 只删除数据

truncate 表名;    -- 删除数据，重置自增长

drop table 表名;  -- 删除数据表

-- select 选项

select * from my_copy;

select all * from my_copy;

-- 去重

select distinct * from my_copy;

-- 向学生表插入数据

insert into my_student values(null,'bc20190001','张三','男'),

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

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

(null,'bc20190004','赵柳','男'),

(null,'bc20190005','六七','男');

-- 字段别名

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),height=floor(rand()20+170);

-- 找学生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); -- in表示在集合中

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

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

select * from my_student where height between 180 and 190;

select * from my_student where height

between 190 and 180; -- 不成立，相当于height>=190 and height<=180

select * from my_student where 1; -- 所有条件都满足；

-- 根据性别分组

select * from my_student group by sex;

-- 分组统计；身高高矮，平均年龄、总年龄

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

sum(age)from my_student group by sex;

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

update my_student set age=null where id=4;

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

sum(age)from my_student group by sex;

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

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

-- nan

-- nv

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

sum(age)from my_student group by sex desc;

-- 删除班级表原主键

alter table my_class drop primary key;

-- 给班级表增加主键

alter table my_class add id int primary key auto_increment;

-- 给学生表增加班级ID

alter table my_student add c_id int;

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

-- 多字段分组；先班组；后男女

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 byc_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() from my_student

where count(

)>=2 group by c_id having by c_id; -- 错误

-- having字句进行条件查询

-- 与where子句一样，是进行条件判断的

-- having能够使用字段别名

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

having  名字 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 4,2;

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

alter table my_class change id id int first;

-- 交叉连接

select * from my_student cross join my_class;

-- my_student cross join my_class 是数据源；

-- 内连接

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_student as c on s.c_id=c.id;

-- 把学生表ID为5的记录的c_ID设置为null

update my_student set c_id=null where id=5;

-- where 代替on

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

from my_student as s inner join

my_student as c where s.c_id=c.id;

-- 左连接

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

from my_student as s left join

my_student 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_student as c -- 右表为主表；最终记录数至少不少于左表已有的记录数

on s.c_id=c.id;

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

from my_student as c right join

my_student as s -- 左表为主表；最终记录数至少不少于左表已有的记录数

on s.c_id=c.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;-- 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,'韩信',3);

-- 更新父表的记录

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

update my_foreign2 set c_id=2 where id=4;-- 更新

update my_class set id=4 where id=3; -- 可以；没有学生引用此班级

-- mysql中添加外键约束遇到一下情况：

-- cannot add foreign key constraint

-- 出现这个问题的原因是，外键的使用：

-- 1. 外键字段不能为该表的主键；

-- 2. 外键字段参考字段必须为参考表的主键

-- 插入数据

insert into my_foreign1 values (

null,'马超','3'

);

-- 增加外键

alter table my_foreign1 add

foreign key(c_id) references

my_class(id); -- 失败；因为没有3号班了

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

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 age asc limit 9999999)

union

(select * from my_student

where sex='女'

order by age desc limit 9999999);

select * from my_student where

c_id=(

-- 标量子查询

select id from my_class where

c_name='python1903'); -- id一定只有一个值（一行一列）

insert into my_class values (1,

'python1907','B407');

-- 列子查询

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);

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); -- 所有结果（null除外）

select * from my_student where

c_id!=all(select id from my_class); -- 所有2号班级（null除外）

select * from my_student where

age=(select max(age) from

my_student)

and

height=(select max(height)) from

my_student);

-- 行子查询

select * from my_student

-- (age,height)称之内为行元素

where (age,height)=(select max(

age),max(height) from my_student);

update my_student set height=188

where name='王五';

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 height desc

limit 9999999)as student group  --》student自己起的名字

by c_id;

select  exists (select * from

my_student);

select  exists (select * from

my_student where id=100);

-- exists子查询

select * from my_student where

exists (select * from my_class

where id=1); -- 是否成立

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;

多表视图更新数据

update my_v3 set c_id=3 where id=5;

-- 但是用内连接生成的视图可以更新数据

-- 左外连接后的结果是包括左表中的全部行，与右表中没有对应的数据填充null,内连接只连接两表中等值的部分

create view my_v13 as

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

from my_student as s

join my_class as c

on s.c_id=c.id;

-- 查看建视图语句

show create view my_v13\G

-- 查看视图所有数据

select * from my_v3;

select * from my_v13;

-- 多表视图更新数据

update my_v13 set c_id=3 where id=5;--成功

-- 查看视图创建语句

show create view my_v3\G  -- \G 横向查看

-- 视图使用

select * from my_v1;

select * from my_v2;

select * from my_v3;

-- 修改视图

alter view my_v1 as

select id,name,sex,age,height,c_id

from my_student;

-- 创建视图

create view my_v4 as select * from my_student;

-- 删除视图

drop view my_v4;

-- 多表视图插入数据

insert into my_v3

values(null,'bc20190006','张三丰','男',

150,180,1,'python1907','B407');

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

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,'python1811','B410');

-- 多表视图删除数据

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=29 where id=3;

-- 可以修改数据；可以改；但是视图查不到

update my_v4 set age=32 where id=2;

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

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;

-- 查看MySQL的版本

select @@version;

-- 创建myisam表

create table my_myisam(

id int

) charset utf8 engine=myisam;

-- 向my_myisam表插入几条记录

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

-- 单表的数据备份 --

-- ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

-- show variables like '%secure%';

-- 找到secure_file_priv        | C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

-- 打开my文件，ctrl+f找到关键字secure,修改原路径：

-- 把原路径用#注释掉，改为:

-- secure-file-priv=""

-- 需要重启MySQL服务并重新登录认证

-- 单表的数据备份

select * into outfile

'D:/t/我的文档/19071/web2/student.txt' from my_student;

-- 指定备份处理方式

select * into outfile

'D:/t/我的文档/19071/web2/class1.txt'

-- 字段处理

fields

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

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

-- 行处理

lines

starting by 'START:'

from my_class;

delete from my_class;

-- 还原数据

load data infile

-- 文件所在路径

'D:/t/我的文档/19071/web2/class1.txt'

into table my_class  -- 表名[(字段列表)]

-- 字段处理

fields

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

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

-- 行处理

lines

starting by 'START:';

-- SQL备份 不是SQL语句\q退出  -- 常用的        -- 不能换行

mysqldump -uroot -p123456 mydatabase my_student > D:/t/我的文档/19071/web2/student.sql

-- 整库备份

mysqldump -uroot -p123456 mydatabase > D:/t/我的文档/19071/web2/mydatabase.sql

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

mysql -uroot -p123456 mydatabase < D:/t/我的文档/19071/web2/student.sql

-- SQL指令还原SQL备份

source D:/t/我的文档/19071/web2/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,'1234567890000000','张三','1000'),

(null,'1234567890000001','李四','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;  --off/0

-- 给李四发工资

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

commit;

-- 银行扣税

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

-- 事物的隔离性

start transaction;

-- 给张三返税，返500块钱

update my_account set money=money+500

where id=1;

--另外窗口开启事物    start transaction;--李四淘宝花了500update my_accountsetmoney=money-500whereid=2;select*frommy_account;commit;select*frommy_account;

-- 回到张三窗口；事物回滚

rollback;

select * from my_account; -- 两边一致

-- 锁机制

start transaction;

-- 使用非索引字段（name），行锁自动上升为表锁

update my_account set money=money+500

where name='张三';

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

=================================================================

-- SQL演练

-- 查询类型cate_name为'超级本'的商品名称，价格

select * from goods where cate_name='超级本';

select name as 商品名称,price as 商品价格

from goods where cate_name='超级本';

-- 显示商品的种类

select distinct cate_name from goods;

select cate_name from goods group by cate_name;

select cate_name,group_concat(name)

from goods group by cate_name;

-- 求所有电脑产品的平均价格,并且保留两位小数

select round(avg(price),2) from goods;

-- 显示每种商品的平均价格

select cate_name,avg(price) from goods

group by cate_name;

-- 查询每种类型的商品中 最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),

count(*) from goods group by cate_name;

-- 查询所有价格大于平均价格的商品，并且按价格降序排序

select * from goods where  price>

(select avg(price) from goods)

order by price desc;

-- 查询每种类型中最贵的电脑信息

select cate_name,max(price) from

goods group by cate_name;

select *

insert into goods values

(0,'东哥牌电脑','笔记本','老王','4999',default,default);

-- 查询每种类型中最贵的电脑信息

select * from goods

inner join

(

select

cate_name,

max(price) as max_price,

min(price) as min_price,

avg(price) as avg_price,

count(*) from goods group by cate_name

) as goods_new_info

on goods.cate_name=goods_new_info.cate_name and

goods.price=goods_new_info.max_price;

select  g_new.cate_name,g.name,g.price

from (select cate_name,max(price) as

max_price from goods group by cate_name)

as g_new left

join goods as g

on g_new.cate_name=g.cate_name

and g_new.max_price = g.price order by g_new.cate_name;

-- 创建商品分类表

create table if not exists goods_cates(

id int unsigned primary key auto_increment,

name varchar(40) not null

);

-- 查询goods表中商品的种类

select cate_name from goods group by cate_name;

-- 拆表

-- 向表中插入

-- 将分组结果写入到goods_cates数据表

insert into goods_cates (name)

select cate_name from goods

group by cate_name;

-- 通过goods_cates数据表来更新goods表

update goods as g inner join goods_cates

as c on g.cate_name=c.name

set g.cate_name=c.id;

-- 插入类别

insert into goods_cates(name)

values ('路由器'),('交换机'),('网卡');

-- 插入商品

insert into goods (name,cate_name,brand_name,price)

values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');

-- 修改表结构

alter table goods

change cate_name cate_id int

unsigned not null;

delete from goods where id=23;

-- 添加外键

alter table goods

add foreign key (cate_id)

references goods_cates(id);

-- 插入商品

insert into goods (name,cate_name,brand_name,price)

values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849'); -- 失败

-- 作业；拆品牌表

create table goods_brands (

id int unsigned primary key auto_increment,

name varchar(40) not null) select

brand_name as name from goods group by brand_name;

update goods as g inner join goods_brands as

b on g.brand_name=b.name set g.brand_name=b.id;

-- 创建测试表

create table test_index(title varchar(10));

-- 开启运行时间监测： 统计操作时间

set profiling=1;

-- 查找第10万条数据ha-99999

select * from test_index where title='ha-99999';

-- 查看执行的时间：

show profiles;

-- 为表title_index的title列创建索引：

create index title_index on test_index(title(10));

-- 执行查询语句：

select * from test_index where title='ha-99999';

-- 再次查看执行的时间

show profiles;

-- 查看索引

show index from test_index;

show index from goods;

-- 权限

use mysql;

show tables;

select user,host from user;

-- authentication_string是密码

select host,user,authentication_string from user;

-- 创建账户并授予所有权限

grant select on jd.* to 'laoweng'@'localhost' identified by '123456';

-- 使用老翁账户登录

mysql -ulaoweng -p123456

use jd;

select * from goods;

select * from goods_cates;

-- 没有权限；无法修改（也无法删除）

update goods_cates set name="abc" where id=20;

-- 创建一个laoli的账号，密码为12345678，可以任意电脑进行链接访问, 并且对jing_dong数据库中的所有表拥有所有权限

grant all privileges on jd.* to

"laoli"@"%" identified by "12345678";

use mysql;

select user,host from suer;

mysql -ulaoli -p12345678

show databases;

use jd;

show tables;

select * from goods; -- 查Ok

update goods set name="豪哥盘电脑" where id=22; -- 改OK

-- 修改密码

-- 使用root登录，修改mysql数据库的user表

-- 使用password()函数进行密码加密

update user set authentication_string=password('新密码') where user='用户名';

-- 例：

update user set authentication_string=password('123') where user='laoweng';

-- 注意修改完成后需要刷新权限

-- 刷新权限：

flush privileges

-- 备份

mysqldump -uroot -p123456 jd > jd.sql

-- 主从

-- 在主服务器上进行备份

mysqldump -uroot -p123456

作业:修改和删除分类

增删改品牌

-- 设置连接到master主服务器

change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;

-- 创建购物车表

create table carts(

id int unsigned primary key auto_increment,

customer_id int unsigned not null

)charset utf8;

-- 创建购物项表

create table cart_detail(

id int unsigned primary key auto_increment,

cart_id int unsigned not null comment'购物车id',

good_id int unsigned not null comment '商品id',

quantity int unsigned not null comment '数量',

price decimal (10,2) not null comment '价格'

)charset utf8;

insert into carts values(0, 3);

insert into cart_detail values(0, 1, 22, 1, 4999.000);

select * from carts as c,cart_detail as d where c.customer_id=3 and d.good_id=22;

-- 给商品表增加图片字段

alter table goods add img varchar (200);

-- 给商品表的图片字段插入数据

update goods set img ="img/1.jpg" where id=1;

update goods set img ="img/2.jpg" where id=2;

update goods set img ="img/3.jpg" where id=3;

update goods set img ="img/4.jpg" where id=4;

update goods set img ="img/5.jpg" where id=5;

update goods set img ="img/6.jpg" where id=6;

update goods set img ="img/7.jpg" where id=7;

update goods set img ="img/8.jpg" where id=8;

update goods set img ="img/9.jpg" where id=9;

update goods set img ="img/10.jpg" where id=10;

-- 修改商品表的图片字段不允许为空

alter table goods modify img varchar(200) not null;

-- 查询用户编号=3的购物车信息

select g.img,g.name,d.price,d.quantity

from carts as c,cart_detail as d,goods as g

where customer_id=3 and c.id=d.cart_id and d.good_id=g.id;

-- 给用户增加邮箱字段

alter table customers add email varchar(100);

-- 修改用户表的地址电话字段允许为空

alter table customers address varchar(150) default null;

-- alter table customers address varchar(150) default null;

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,009评论 5赞 474
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,808评论 2赞 378
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 148,891评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,283评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,285评论 5赞 363
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,409评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,809评论 3赞 393
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,487评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,680评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,499评论 2赞 318
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,548评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,268评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,815评论 3赞 304
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,872评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,102评论 1赞 258
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,683评论 2赞 348
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,253评论 2赞 341

推荐阅读更多精彩内容

  • 摘录的一些知识点
    ORACLE自学教程 --create tabletestone ( id number, --序号usernam...
    落叶寂聊阅读 1,062评论 0赞 0
  • MySQL
    第1章 初涉MySQL 1.1 MySQL文件 （1）MySQL目录结构 （2）MySQL配置向导文件（安装后配置...
    凛0_0阅读 771评论 1赞 0
  • mysql练习题
    Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 S...
    忘了呼吸的那只猫阅读 2,827评论 0赞 8
  • 一、数据库
    一、数据库操作： 1.1 创建数据库： create database student； 1.2 删除数据库： ...
    __71db阅读 777评论 0赞 0
  • 初涉MySQL
    启动/停止/重启MySQL服务 启动MySQL服务： mysql.server start 停止MySQL服务:...
    _Sisyphus阅读 537评论 1赞 2