学习感悟

我的标签

随笔分类

部署文档(18)

Python学习之路(25)

随笔档案

Day_12_总结_MySQL基础学习目录

MySQL基础学习目录

Mysql命令以;或者\g结束

\c 语句不完整，需要重新输入

\s 查看数据库信息

基础知识:

1.数据库的连接

mysql -u -p -h

-u 用户名

-p 密码

-h host主机

2:库级知识

2.1 显示数据库: show databases;

2.2 选择数据库: use dbname;

select * from user \G; 查询用户单个显示

2.3 创建数据库: create database dbname charset utf8;

create database day12;

2.3 删除数据库: drop database dbname;

3: 表级操作:

3.1 显示库下面的表

show tables;

3.2 查看表的结构:

desc tableName;

3.3 查看表的创建过程:

show create table tableName;

show create table user;

3.4 创建表:

create table tbName (

列名称1 列类型 [列参数] [not null default ],

....列2...

....

列名称N 列类型 [列参数] [not null default ]

)engine myisam/innodb charset utf8/gbk

#创建一张表

create table user(

#列名列类型 [是否为null 默认值]

id int

);

create table user(

id int not null default 0

);

desc user; #查看表属性

举例：

#插入一条数据

insert into user (id) values (2);

insert into user (id) values (-3);

3.4的例子:

create table user (

id int auto_increment,

name varchar(20) not null default '',

age tinyint unsigned not null default 0,

index id (id)

)engine=innodb charset=utf8;

注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,

charset 常用的有utf8,gbk;

3.5 修改表

3.5.1 修改表之增加列:

alter table tbName

add 列名称１列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样)

#添加列表

alter table 表名 add 列名类型

举例：

alter table user add school varchar(64) not null default '' ;

alter table user add address varchar(64) not null default '' after age ; #指定列表添加位置

举例：

insert into user(name,email,age,school) values('Frank','frank@163.com',18,'上海');

3.5.2 修改表之修改列

alter table tbName

change 旧列名新列名列类型 [列参数] [not null default ]

(注:旧列名之后的语法和创建表时的列声明一样)

举例：

alter table user change school xuexiao varchar(64) not null default '' ;

3.5.3 修改表之减少列:

alter table tbName

drop 列名称;

alter table user drop address; #删除列

3.5.4 修改表之增加主键

alter table tbName add primary key(主键所在列名);

例:alter table goods add primary key(id)

该例是把主键建立在id列上

3.5.5 修改表之删除主键

alter table tbName drop primary key;

3.5.6 修改表之增加索引

alter table tbName add [unique|fulltext] index 索引名(列名);

3.5.7 修改表之删除索引

alter table tbName drop index 索引名;

3.5.8 清空表的数据

truncate tableName;

4:列类型讲解

列类型:

整型:tinyint (0~255/-128~127) smallint (0~65535/-32768~32767) mediumint int bigint (参考手册11.2)

参数解释:

unsigned 无符号(不能为负) zerofill 0填充 M 填充后的宽度

举例:tinyint unsigned;

tinyint(6) zerofill;

数值型

浮点型:float double

格式:float(M,D) unsigned\zerofill;

举例：

#unsigned 无符号，存放数据为正数

create table test(

id int unsigned not null default 0

);

insert into test (id) values (2);

insert into test (id) values (-3);

字符型

char(m) 定长

varchar(m)变长

text

列实存字符i 实占空间利用率

char(M) 0<=i<=M M i/m<=100%

varchar(M) 0<=i<=M i+1,2 i/i+1/2<100%

year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)

日期时间类型 date YYYY-MM-DD 如:2010-03-14

time HH:MM:SS 如:19:26:32

datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32

timestamp YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间

举例：

create table test1(

id int unsigned not null default 0,

name varchar(64) not null default '',

ctime datetime not null default CURRENT_TIMESTAMP COMMENT '创建时间'

);

insert into test1 (id,name,ctime) values (1,'Frank','2018-10-10 10:46:00');

create table user(

id int not null auto_increment primary key,

name varchar(64) not null default '',

email varchar(128) not null default '',

age tinyint not null default 0

);

insert into user(name,email,age) values('Frank','frank@163.com',18);

insert into user(name,email,age) values('Sunny','Sunny@163.com',19);

5:增删改查基本操作

5.1 插入数据

insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列

insert into 表名 values (,,,,); -- 插入所有列

insert into 表名 values -- 一次插入多行

(val1,val2……),

(val1,val2……);

举例：

insert into user(name,email,age) values('Frank','frank@163.com',18),('Sunny','Sunny@163.com',19);

insert into user(name,email,age) select name,email,age from user;

5.3修改数据

update tablename

set

col1=newval1,

col2=newval2,

...

colN=newvalN

where 条件;

5.4，删除数据 delete from tablenaeme where 条件;

举例：

delete from user; #啄行删除数据，如果有自增id，会接着上个id增加，数据量大，删除慢

select * from user;

insert into user(name,email,age) values('Frank','frank@163.com',18);

select * from user;

truncate table user;#会很快删除数据，如果有自增id，会重新开始增加，数据量大，删除快

select * from user;

insert into user(name,email,age) values('Frank','frank@163.com',18);

delete from user where id =1;

日常工作使用方法：

alter table user add status tinyint not null default 0 ;

update user set status=1 where id =2;

select * from user where status=0;

5.5， select 查询

（1）条件查询 where a. 条件表达式的意义，表达式为真，则该行取出

b. 比较运算符 = ，!=，< > <= >=

c. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符)

in , not in , between and

d. is null , is not null

（2）分组 group by

一般要配合5个聚合函数使用:max,min,sum,avg,count

（3）筛选 having

（4）排序 order by

（5）限制 limit

高级查询

1.where条件查询

条件：

比较运算符（> < = >= <=）

逻辑运算符（!= and or）

2.limit offset 取的条数

3.order by 默认升序(asc) 降序(desc)

4.group by 分组配合聚合函数（max,min,avg,count,sum）

写的顺序

where>group by(having) > order by > limit

举例：

group by > order by > limit

select * from user group by age order by status limit 1 ;

select * from user order by age;

select * from user order by age desc ;

select * from user group by name;

select count(id) from user;

select sum(age) from user;

select age from user group by age having max(age)<18;

select *from user where age =18 and id >3;

select * from user where name like 'Fra%';

select * from user limit 2,3; #2代表偏移量，从哪个位置开始取值，默认前几条，用在分页

6:连接查询

6.1，左连接

.. left join .. on

table A left join table B on tableA.col1 = tableB.col2 ;

例句:

select 列名 from table A left join table B on tableA.col1 = tableB.col2

2. 右链接: right join

3. 内连接: inner join

举例：

create table student(

id int not null auto_increment primary key,

name varchar(32) not null default '',

qq varchar(32) not null default '',

age tinyint not null default 0,

cid int not null default 0

);

insert into student(name,qq,age,cid) values('frank',200890836,18,1),('sunny',200890837,19,2),('franksunny',200890838,20,1);

create table class_list(

id int not null auto_increment primary key,

class_name varchar(32) not null default ''

);

insert into class_list(class_name) values('Python自动化'),('Linux自动化运维'),('数据分析');

select * from student as a left join class_list as b on a.cid=b.id;

select a.id,a.name,a.age,b.class_name from student as a left join class_list as b on a.cid=b.id;

一般不建议使用，分开进行查询的速度比联合查询的速度快，前提是索引建立好

左右连接都是以在左边的表的数据为准,沿着左表查右表.

内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.

7子查询

where 型子查询:内层sql的返回值在where后作为条件表达式的一部分

例句: select * from tableA where colA = (select colB from tableB where ...);

from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询

例句:select * from (select * from ...) as tableName where ....

举例：

select * from class_list where id in (select cid from student);

8: 字符集

客服端sql编码 character_set_client

服务器转化后的sql编码 character_set_connection

服务器返回给客户端的结果集编码 character_set_results

快速把以上3个变量设为相同值: set names 字符集

#查看字符集

show variables like '%character%';

set names utf8;

set character_set_results='utf8'

保证文件编码，数据库编码，网站页面编码（html）都是一致(utf-8)

show character set like 'lat%';

show character set like 'gbk%';

存储引擎 engine=1\2

1 Myisam 速度快不支持事务回滚表锁

2 Innodb 速度慢支持事务,回滚行锁

myIsam和Innodb的区别

1.Innodb支持事物，myIsam不支持

2.Innodb在高并发情况下，支持行锁，myIsam支持表锁

3.Innodb支持外键，myIsam不支持外键

增删改查 C:create R:retrive U:update D:delete

①开启事务 start transaction

②运行sql;

③提交,同时生效\回滚 commit\rollback

user.frm #保存表的结构

user.ibd #保存表的数据和索引

# Server version: 8.0.12

only_full_group_by说明：

select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，这个配置和distinct差不多的，所以去掉就好，如果时间和日期为空把NO_ZERO_IN_DATE,NO_ZERO_DATE去掉

select @@sql_mode;

#去掉ONLY_FULL_GROUP_BY

set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

或者

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

综合练习

练习题1要求：

连接上数据库服务器

创建一个utf8编码的数据库

建立商品表和栏目表,字段如下:

商品表:goods

goods_id --主键,

goods_name -- 商品名称

cat_id -- 栏目id

brand_id -- 品牌id

goods_sn -- 货号

goods_number -- 库存量

shop_price -- 价格

goods_desc --商品详细描述

栏目表:category

cat_id --主键

cat_name -- 栏目名称

parent_id -- 栏目的父id

建表完成后,作以下操作:

删除goods表的goods_desc 字段,及货号字段

并增加字段:click_count -- 点击量

在goods_name列上加唯一性索引

在shop_price列上加普通索引

在clcik_count列上加普通索引

删除click_count列上的索引

#创建表goods

create table goods(

goods_id int not null auto_increment primary key,

goods_name varchar(128) not null default '',

cat_id int unsigned not null default 0,

brand_id int unsigned not null default 0,

goods_sn int unsigned not null default 0,

goods_number int not null default 0,

shop_price int not null default 0,

goods_desc varchar(128) not null default '',

parent_id int unsigned not null default 0

);

create table category(

cat_id int not null auto_increment primary key,

cat_name varchar(128) not null default ''

);

#修改列属性

alter table goods drop goods_desc;

alter table goods drop goods_sn;

alter table goods drop parent_id;

alter table goods add goods_sn int unsigned not null default 0 after brand_id;

alter table goods change goods_sn goods_sn varchar(128) not null default '';

alter table goods add click_count int unsigned not null default 0;

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ()

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('KD876',4,8,'ECS000000',10,1388,7);

insert into goods values('KD876',4,8,'ECS000000',10,1388,7);

#查询功能使用

select goods_id,goods_name,shop_price from goods where goods_id =18;

select goods_id,goods_name,shop_price from goods where cat_id !=3;

select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 3000;

select goods_id,cat_id,goods_name,shop_price from goods where shop_price <=100;

select goods_id,cat_id,goods_name,shop_price from goods where cat_id in (4,11);

select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 500;

select goods_id,cat_id,goods_name,shop_price from goods where goods_name not like '诺基亚%';

select goods_id,cat_id,goods_name,shop_price from goods where shop_price>100 and shop_price<300 or shop_price>4000 and shop_price<5000;

select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 and shop_price>1000 and shop_price <3000 and click_count >5 and goods_name like '诺基亚%' ;

select max(shop_price) from goods;

select sum(goods_number) from goods;

select goods_id,cat_id,goods_name,shop_price from goods order by shop_price limit 3;

#插入数据

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('KD876',4,8,'ECS000000',10,1388,7);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚N85原装充电器',8,1,'ECS000004',17,58,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚原装5800耳机',8,1,'ECS000002',24,68,3);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('索爱原装M2卡读卡器',11,7,'ECS000005',8,20,3);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('胜创KINGMAX内存卡',11,0,'ECS000006',15,42,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚N85原装立体声耳机HS-82',8,1,'ECS000007',20,100,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('飞利浦9@9v',3,4,'ECS000008',17,399,9);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚E66',3,1,'ECS000009',13,2298,20);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('索爱C702c',3,7,'ECS000010',7,1328,11);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('索爱C702c',3,7,'ECS000011',1,1300,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('摩托罗拉A810',3,2,'ECS000012',8,983,14);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚5320XpressMusic',3,1,'ECS000013',8,1311,13);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚5800XM',4,1,'ECS000014',4,2625,6);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('摩托罗拉A810',3,2,'ECS000015',3,788,8);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('恒基伟业G101',2,11,'ECS000016',0,823.33,3);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('夏新N7',3,5,'ECS000017',1,2300,2);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('夏新T5',4,5,'ECS000018',1,2878,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('三星SGH-F258',3,6,'ECS000019',0,858,7);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('三星BC01',3,6,'ECS000020',13,280,14);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('金立A30',3,10,'ECS000021',40,2000,4);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('多普达TouchHD',3,3,'ECS000022',0,5999,15);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚N96',5,1,'ECS000023',8,3700,17);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('P806',3,9,'ECS000024',148,2000,36);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('小灵通/固话50元充值卡',13,0,'ECS000025',2,48,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('小灵通/固话20元充值卡',13,0,'ECS000026',2,19,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('联通100元充值卡',15,0,'ECS000027',2,95,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('联通50元充值卡',15,0,'ECS000028',0,45,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('移动100元充值卡',14,0,'ECS000029',0,90,0);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('移动20元充值卡',14,0,'ECS000030',9,18,1);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('摩托罗拉E8',3,2,'ECS000031',1,1337,5);

insert into goods (goods_name,cat_id,brand_id,goods_sn,goods_number,shop_price,click_count) values ('诺基亚N85',3,1,'ECS000032',1,3010,9);

练习题2要求：

#创建表Math

create table Math(

matchID int unsigned not null auto_increment primary key,

hostTeamID int unsigned not null default 0,

guestTeamID int unsigned not null default 0,

matchResult varchar(20) not null default '',

matchTime date not null default '0000-00-00'

);

#插入数据

insert into Math values(1,1,2,'2:0','2006-05-21');

insert into Math values(2,2,3,'1:2','2006-06-21'),(3,3,1,'2:5','2006-06-25'),(4,2,1,'3:2','2006-07-21');

#创建表Team

create table Team(

teamID int unsigned not null auto_increment primary key,

teamName varchar(20) not null default ''

);

#插入数据

insert into Team values (1,'国安'),(2,'申花'),(3,'传智联队');

#查询

select a.hostTeamID,b.teamName,a.matchResult,a.guestTeamID,c.teamName,a.matchTime from Math as a left join Team as b on a.hostTeamID=b.teamID left join Team c on a.guestTeamID=c.teamID;

返回顶部

Frank的学习之路

随笔分类

随笔档案

Day_12_总结_MySQL基础学习目录