MySQL基本操作

 

 

 

1.MySQL

Mysql官网：https://www.mysql.com/

https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

 

1.1安装MySQL

# hostnamectl --static set-hostname mysql   //修改主机名
# exit
​
# cd /tmp
# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# yum install mysql80-community-release-el7-3.noarch.rpm
# ls /etc/yum.repos.d/
# yum repolist      //刷新yum源
# yum repolist all |grep mysql      //默认开启mysql80
​
# yum -y install yum-utils      //yum管理包
# yum-config-manager --disable mysql80-community
# yum repolist all |grep mysql
# yum-config-manager --enable mysql57-community
# yum repolist enabled |grep mysql      //只允许开启一个版本

或通过手动编辑/etc/yum.repos.d/mysql-community.repo 文件来选择发行系列 刷新yum

# vim /etc/yum.repos.d/mysql-community.repo
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

 

# yum -y install mysql-community-server mysql
# systemctl start mysqld
# ls /var/lib/mysql         //查看mysql安装目录
# systemctl status mysqld
# systemctl enable mysqld
# grep 'tqfeduorary password' /var/log/mysqld.log
xxxxxx
​
# mysql -uroot -p'xxxxxx'
> show database;
需先修改密码
> alter user 'root'@'localhost' identified by 'A.123com';
> show database;
> grant select,drop,insert on *.* to 'zhangsan'@'localhost' identified by 'A.123com';
> select user,host from mysql.user;
> flush privileges;     //刷新权限
> show grants for 'zhangsan'@'localhost';   //查看用户权限

 

2.结构化查询语言

1.2数据定义语言DDL

  • Data Dafinitaon Language

  • 如创建表 create

  • 删除表drop

  • 修改表alter

  • 清空表truncate，彻底清空，无法找回

# mysql -uroot -p'xxxxxx'
> use mysql;
> show tables;
​
> create database db1;
> drop database db1;        //删库
​
> create database db1 default character set utf8;
> use db1;
​
> create table t1(id int(3),name varchar(20));
> show tables;
​
> insert into t1 values(1,'user1');
> select * from t1;
> desc t1;      //查看表结构
​
> alter table t1 add(age int(3));       //添加字段
> alter table t1 add(age int(3)) first; //字段添加到前面
> desc t1;
> insert into t1 values(2,'user2',10);
​
​
> alter table t1 drop id;           //删除表字段
> alter table t1 modify age varchar(2);     //修改表字段类型格式
> alter table t1 change age plage int(3);   //修改字段名称
> alter table t1 rename per;            //修改表名
> truncate table t1;        //清空表结构
> drop table t1;            //删除表结构

　　

1.3数据操纵语言DML

  • insert：向表中插入数据

  • delete：删除表中的数据，格式：delete from tablname [where 条件]

  • update：修改表中的数据，格式：update tablname set coIName=value[,coIlName2=value2] [where 条件]

  • where：对表中的数据增加条件进行限制，起到过滤的作用。

1.格式：where coIName 关系运算符 value [or | and 条件2]

2.关系运算符：>, >=, <, <=, =, 不等于：!=或<>

  • null值操作：比较null时，不能使用=或者!=或者<>,而是使用is或者is not，在select子句中，使用关系运算符。

> create table t1(id int,name varchar(20),birth data,address varchar(50));
> show tables;
> desc t1;
​
//插入数据
> insert into t1 values(1,'user1',null,'shanghai');
> insert into t1(id,name,address) values(2,'user2','shanghai');
> insert into t1(id,name,address) values(3,'user3','shanghai'),(4,'user4','guangzhou'),(5,'user5','beijing');
​
//删除数据
> delete from t1 where id=3;        
> delete from t1 where id=3 and address=shenzheng;
> delete from t1 where id=3 or address=shenzheng;
> delete from t1;
​
//修改数据
> insert into t1(id,name,address) values(3,'user3','shanghai'),(4,'user4','guangzhou'),(5,'user5','beijing');
> update t1 set address='china';    
> update t1 set address='shanghai',name='lisi' where id=3;
> update t1 set name='zhangsan' where birth is null;

1.4事物控制语言TCL

  • 事物条件：

  1. 原子性： 事务是不可分割的一个单位，事务中操作要么都执行，要么都不执行。

  2. 一致性： 事务必须从一个一致性状态到另一个一致性状态。

  3. 隔离性： 一个事务执行不被另一个事务干扰。

  4. 持久性： 一个事务一旦被提交，对数据库数据是永久性的。

  • commit 提交

  • rollback 撤回，回滚。

  • savepoint 保存点

  • 只有DML操作会触发一个事务。存储引擎(ENGINE):就是指表类型。当存储引擎为innodb时，才支持事物。默认的存储引擎为Myisam。不支持事务。

  • 事务验证：

    • 第一步：start transaction(交易)

    • 第二步：savepoint保存点名称。

    • 第三步：DML

    • 第四步：commit/rollback;

1.5数据查询语言DQL

//创建表
> create table t1(id int,name varchar(10),job varchar(10),mgr int(4),hiredate DATE,sal int(7),comm int(7),deptid int(2));
> insert into t1(id,name,job,mgr,hiredate,sal,comm,deptid) values
(0001,'zhangsan',"xxx",1234,'1980-12-12',800,NULL,1),
(0002,'lisi',"xxx",1235,'1981-2-1',1800,100,1),
(0003,'wangwu',"xxx",1236,'1982-4-5',1200,NULL,1),
(0004,'zhaoliu',"xxx",1234,'1989-2-2',800,NULL,1)
> select name,job,sal,deptid from t1;
​
//查询
> select name as 'mingzi',job as 'zhiwei' from t1;
> select id,name,job,sal,deptid from t1 where deptid=10 or deptid=20;
> select * from t1 where deptid<>10 and deptid=20;
> select * from t1 where deptid in(10,20);
> select * from t1 where deptid not in(10,20);
​
//All|Any与集合连用
​
//查询大于这三名员工sal的信息
> select * from t1 where sal>all(select  sal from t1 where name in('zhangsan','lisi','wangwu'));
//查询工资大于等于1500且小于等于2500的员工
> select * from t1 where sal between 1500 and 2500;
> select * from t1 where sal not between 2000 and 2500;
​
//like模糊搜索
> select * from t1 where name like '%a%' and name like '%s%';
> select * from t1 where name like '%a%s%' and name like '%s%a%';

 

1.5.1排序查询order by子句

//排序查询order by子句
> select * from t1 order by sal DESC;       //倒序
> select * from t1 order by sal ASC;        //升序，默认

　　

1.5.2Distinct去重

> insert into t1(id,name,job,mgr,hiredate,sal,comm,deptid) values(0005,'zhangsan',"xxx",1234,'1980-12-12',800,NULL,1);
> select distinct name from t1;     //去重

 

1.5.3分组查询Group by子句

//分组查询，select子句中的字段，除了聚合函数外，只能写分组字段。
> select deptid,avg(sal) as av from t1 group by deptid;

  • 聚合函数：

    • count(Filed)统计指定字段的记录数。

    • sum(Filed)统计指定字段的和。

    • avg(Filed)统计指定字段的平均值。

    • max(Filed)返回指定字段中的最大值。

    • min(Filed)返回指定字段中的最小值

  • 聚合函数会忽略null值。因此有时候需要使用函数；ifnull(field,value)（如果field字段对应的值不是null，就是用field的值，如果是null，就是用value）。

  • 多字段分组是（多表联合查询时不加任何条件），最多分组的数目为Filed1Filed2(Filed3....)

1.5.4分组查询添加条件Having子句

//分组查询添加条件Having子句
> select deptid,avg(sal) as av from t1 group by deptid having avg(sal)>2000;

  • 在分组查询时，有的时候可能需要再次使用条件进行过滤，这个时候不能使用 where 子句，应该使用having子句。having子句后可以使用聚合函数。

  • 位于group by子句后

1.5.5

  • 基本查询语句的子句：

    • select子句

    • from子句

    • where子句

    • group by子句

    • having子句

    • order by子句

select..from..[where..][group by][having..][order by..]

执行顺序：from子句，where子句，group by子句，having子句，select子句，order by子句

1.5.6高级关联查询

  • 查询的数据一个简单的查询语句满足不了，并且使用的数据在表中不能直观体现出来。而是需要预先经过一次查询才会有所体现。先执行的子查询。子查询嵌入到的查询语句称之为父查询。

  • 子查询返回的数据特点：

    • 可能是单行（多行）单列、单行（多行）多列的数据。

  • 子查询可以在where、from、having、select字句中，在select子句中时相当于外连接的另一种写法

//avg(ifnull(sal,0))，如果为空，使用指定的值，
> select deptid,avg(ifnull(sal,0)) as av from t1 group by deptid;   //赋值给b
//查询表中各部门人员中大于部门平均工资的人
> select name,sal,a.deptid,b.av from t1 a,(select deptid,avg(ifnull(sal,0)) as av from t1 group by deptid) b where a.deptid=b.deptid and a.sal>b.av order by deptid ASC;

　　

1.6数据控制语言DCL

数据控制语言Data Control Language，作用是用来创建用户，给用户授权，撤销权限，删除用户。

1.6.1创建用户

> create user username@IP identified by newPwd;
> create user 'zhangsan'@'192.168.0.2' identified by 'A.123com';
> create user 'zhangsan'@'192.168.0.%' identified by 'A.123com';

 

1.6.2显示用户的权限

> select user,host from mysql.user;
> show grants for username@ip;

1.6.3授权

> create database test;
> use test;
> create table t1(id int,name varchar(20));
> grant select,drop,insert on test.* to 'zhangsan'@'192.168.0.2';
> \q
# mysql -h192.168.0.2 -uzhangsan -p'A.123com'
> show databases;

1.6.4删除用户

> drop user 'zhangsan'@'192.168.0.2';
> flush privileges;
> select user,host from mysql.user;

3.实例

3.1

> use test;
​
> create table t1(num int auto_increment primary key,
name varchar(10),
job char(10),
age int,
salary int,
descrip char(128)not null default ''
)charset=utf8;
​
> insert into t1(name,job,age,salary,descrip) values
('zhangsan','teacher',30,10000,'level2'),
('lisi','teacher',20,10000,'level2'),
('wangwu','teacher',29,10000,'level3'),
('zhangsan','teacher',25,10000,'level4');

　　

3.1添加字段

> create table t2(id int,age int)charset=utf8;
> alter table t2 add name varchar(20) not null default '';      //添加字段
> alter table t2 add name3 varchar(20) not null default '' first;       //字段添加到前面
> alter table t2 add name4 varchar(20) not null default '' after id;    //添加到指定字段后面
​
//删除字段
> alter table t2 drop name4;
​
//修改字段类型
> alter table t2 modify name char(20);
​
//修改字段名
> alter table t2 change name name2 varchar(20) not null default '';

3.2表操作

//删除表
> drop table t2;
> show tables;
​
//复制表结构（t1新表）
> create table t1 like t2;

 

3.3操作表数据行

//插入数据
> insert into t2(id,name) values(1,'zhangsan');
//
> insert into t1(name) select name from t2;
​
//删除数据
> delete from t2 where id=1;
> truncate
​
//修改数据
> update t2 set name='fugui' where id=1;
​
//查询,between..and..:取值范围是闭区间
> select * from t1;
> select * from t1 where id between 2 and 3;
​
//DISTINCT
> insert into t2(id,name) values(1,'zhangsan');
> select distinct name from t1;
​
//四则运算查询(不建议用)
> alter table t2 add age int;
> insert into t2(id,name) values(2,'lisi');
> select name,age*10 as age from t2;
​
//查询
> select * from t1 where id in (3,5,6);
> select * from t1 where name like 'q%';
​
//查询用name is null作为条件
> create table t3(id int auto_increment primary key,name varchar(20),email varchar(20))charset=utf8;
> insert into t3(email) values('123');
> select * from t3 where name is null;

  • delete之后，插入数据从上一次主键自增加1开始，truncate则是从1开始

  • delete为一行一行删，truncate全选删除

3.4单表操作

select 字段1，2，3 from 表名

where 条件

group by field

having 筛选

order by field [asc|desc]

limit 限制条数

4.多表操作

  • 概念：当在查询时，所需要的数据不再一张表中，可能在两张表或多张表中，此时需要同时操作这些表。即关联查询。

  • 等值连接：在做多张表查询时，这些表中应该存在这有关联的两个字段。使用某一张表中的一条记录与另一张表通过相关联的两个字段进行匹配，组合成一条记录。

  • 笛卡尔积，在做多张表查询时，使用某一张表中的每一条记录都与另一张表的所有记录进行组合。比如表A有x条，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

  • 内连接：只要使用了join on，就是内连接。查询效果与等值连接一样。

  • 用法：

    表A [inner] join 表B on 关联条件

  • 外连接：在做多张表查询时，所需要的数据，除了满足关联条件的数 据外，还有不满足关联条件的数据。此时需要使用外连接。

    • 驱动表(主表)：除了明显满足条件的数据，还需要显示不满足条件的数据的表

    • 从表(副表)：只显示满足关联条件的数据的表

    • 外连接分三种

      左外连接：表A left [outer] join 表B on 关联条件，表A是驱动表，表B是从表

      右外连接：表A right [outer] join 表B on 关联条件，表B是驱动表，表A是从表

      全外连接：表A full [outer] join 表B on 关联条件，两张表的数据不管满不满足条件，都做显示。

  • 自连接：在多表进行关联查询时，这些表的表名是同一个。即自连接。

  • 外键：占用空间少，方便修改数据。

4.1一对多

一行数据对多行数据

语法

constraint 外键 foreign key (被约束的字段) references 约束的表(约束的字段)

> create table t1(id int auto_increment primary key,name varchar(32) not null default '')charset=utf8;
> insert into t1(name) values('lisi'),('zhangsan'),('wangwu');
​
//t2表(dep_id)关联t1(id)
> create table t2(
id int auto_increment primary key,
name varchar(32) not null default '',
depart_id int not null default 1,
constaraint fk_user_depart foreign key (depart_id) references t1(id)) charset=utf8;
> insert into t2(name,depart_id) values('lisi1',1),('zhangsan2',2),('zhangsan3',3);
​
> insert into t2(name,depart_id) values('zhangsan4',4);     //插入失败，主表只有id在1-3，depart_id只能在1-3内,t2表中的id和t1的id没关联
​
> insert into t2(name,depart_id) values('zhangsan4',1);

4.2多对多

//男生表
> create table boy(id int auto_increment primary key,bname varchar(32) not null default '')charset=utf8;
> insert into boy(bname) values('a'),('b'),('c');
​
//女生表
> create table girl(id int auto_increment primary key,bname varchar(32) not null default '')charset=utf8;
> insert into girl(gname) values('d'),('e'),('f');
​
//创建关联表
> create table b2g(id int auto_increment primary key,
bid int not null defautl 1,
gid int not null default 0,
constraint fk_b2g_boy foreign key (bid) references boy(id),
constraint fk_b2g_girl foreign key (gid) references girl(id))charset utf8;
​
> insert into b2g(bid,gid) values(1,1),(1,2),(2,3),(3,3),(2,2);
> select * from boy left join b2g on boy.id=b2g.bid left join girl on girl.id=b2g.gid;
> select bname,gname from boy left join b2g on boy.id=b2g.bid left join girl on girl.id=b2g.gid;

4.3一对一

//创建员工信息表
> create table user(id int auto_increment primary key,name varchar(32) not null default '')charset=utf8;
> insert into user(name) values('a'),('b'),('c');
​
//创建员工工资表
> create table priv(
id int auto_increment primary key,
salary int not null default 0,
uid int not null default 1,
constraint fk_priv_user foreign key (uid) references user(id),unique(uid))charset=utf8;     //unique(uid)唯一约束
> insert into priv(salary,uid) values(2000,1),(2500,2),(3000,3);

4.4多表联查

> select t2.name as uname,t1.name as dname from t2 left join t1 on depart_id=t1.id;

5.数据导出方式

mysqldump -h IP -u 用户名 -p -d 数据库名 > 导出的文件名

> create table t1(id int,name varchar(32));
> insert into t1 values(1,'zhangsan');
​
//备份
# mysqldump -u root -p'A.123com' --all-databases > /data/mysql.sql
# mysqldump -u root -p'A.123com' --databases test > /data/mysql2.sql
# mysqldump -u root -p'A.123com' test t1 > /data/mysql3.sql
​
//数据库内导入
# mysql -uroot -p'A.123com'
> drop table t1;
//导入数据
> source /data/mysql3.sql
​
//命令行导入
> drop databases test;
# mysql -uroot -p'A.123com' < /data/mysql2.sql

　　

6.数据类型

6.1整型

  • TINYINT 1 byte (-128,127) (0,255)

  • SMALLINT 2 bytes (-32768,32767) (0,65535)

  • MEDIUMINT 3 bytes (-8388608,8388607) (016777215)

  • INT或INTEGER 4 bytes (-2147483648,2147483647) (0,4294967295)

  • BIGINT 8 bytes (-9,223,372,036,854,775,808，9223 372036854775807) (0,18446744073709551615)

  • DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

> create table test_tinyint(num tinyint) engine=innodb charset=utf8;
> insert into test_tinyint1 values(127);
​
//unsigned 不区分符号
> create table test_tinyint(num tinyint unsigned) engine=innodb charset=utf8;
> insert into test_tinyint1 values(255);

6.2 int(n)需要注意

  • 无论N等于多少，int永远站4个字节

  • N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但要整形设置了unsigned zerorill才有效

> create table test_int_width(
a int(4),
b int(5) unsigned,
c int(5) unsigned zerofill,
d int(5) unsigned zerofill) engine=innodb charset=utf8;
> insert into test_int_width values(1,1,1,11111111);
> select * from test_int_width;
1   1   00001   11111111

6.3浮点型

  • float(m,d) 4 bytes 单精度浮点型8位精度

  • double(m,d) 8 bytes 双精度浮点型16位精度

> create table test_float(num float(5,2)) engine=innodb charset=utf8;
> insert into test_float values(1.2345);
> insert into test_float values(1000,123);
> select * from test_float;

定点数

  • DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值

  • 浮点数在数据库存放的是近似值，而定点类型在数据库中存放的是精确值。

  • decimal(m,d)参数m<65是总个数，d<30且d<m是小数位。

  • 定点型的数据类型decimal类型

> create table test_decimal(float_num float(10,2),double_num double(20,2),decimal_num decimal(20,2)) engine=innodb charset=utf8;
> insert into test_decimal values(1234512.111,12345678912345678.66,123456789123456789.22)
1234512.12  12345678912345678.00    123456789123456789.22

6.4字符串

  • char(m) 固定长度，最多255个字符

    • mysql处理char类型数据时会将结尾的所有空格处理掉。

    • varchar不会

  • varchar(m) 可变长度，最多65535个字符

  • tinytext 可变长度，最多255个字符

  • text 可变长度，最多65535个字符

  • mediumtext 可变长度，最多2的24次方-1个字符

  • longtext 可变长度，最多2的32次方-1个字符

> create table test_varchar(varchar_value varchar(21844))) engine=innodb charset=utf8;
> create table test_varchar(varchar_value varchar(32761))) engine=innodb charset=utf8mb4;

  • 对于未指定varchar字段not null的表，会有一个字节专门表示该字段是否为null

  • varchar(n)，当M范围为0<=n<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65522个字节

  • 所有英文无论其编码方式，都占一个字节，但对于gbk编码，一个汉字占两个字节，即最大n=65532/2=32766；对于utf8编码，一个汉字占3个字节，即最大M=65532/3=21844

  • utf8mb4编码方式，1个字符最大可能占4个字节，那么varchar(n)，n最大为65532/4=16383

  • varchar(n) n>255时转为tinytext

  • varchar(n) n>500时转为text

  • varchar(n) n>20000时转为mediumtext

6.5二进制数据（Blob）

  • BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小，二Blob是以二进制方式存储，不区分大小写

  • BLOB存储的数据只能整体读出

  • text可以指定字符集，Blob不用指定字符集

  • blob适用于存储例如图片、音频这种文件的二进制数据的

6.6日期时间类型

类型大小 ( bytes)范围格式用途
date 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
time 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
year 1 1901/2155 YYYY 年份值
datetime 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
timestamp 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 
> create table test_time(
date_value date,
time_value time,
year_value year,
datetime_value datetime,
timestamp_value timestamp) engine=innodb charset=utf8;
> insert into test_time values(now(),now(),now(),now(),now());
> select * from test_time;

  • datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间

  • datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖与当前时区

  • 在实际工作中，一张表往往会有两个默认字段，一个记录创建时间而另一个最新一次的更新时间，这种时候可以使用timestamp类型来实现。

6.7 枚举

enum

> create table test_enum(id int,gender enum('male','female')) charset=utf8;
> insert into test_enum(id,gender) values(1,'male');
> insert into test_enum(id,gender) values(2,'female');

 

6.8MySQL数据类型使用建议

  • 在指定数据类型的时候一般采用从小原则，这样对MySQL的运行效率提高。

  • 不需要把数据表设计的太过复杂，功能模块上区分或许对于后切维护更为方便，慎重出现大杂烩数据表

  • 数据库的最后设计结果一定是效率和可扩展性的折中，偏向任何一方都是欠妥的

6.9MySQL选择数据类型的基本原则

  • 根据选定的存储引擎，去诶定如何选择合适的数据类型。

  • MylSAM数据存储引擎和数据列：MylSAM数据表，最好使用固定长度（char）的数据类型代替可变长度（varchar）的数据列。

  • MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用char或varchar列都没有关系。两者都是作为char类型处理的。

  • InnoDB存储引擎和数据列：建议使用varchar类型，对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的char列不一定比使用可变长度varchar列简单，因此，主要的性能因素是数据行使用的存储总量。由于char平均占用的空间多余varchar，因此使用varchar来最小化需要处理得数据行的存储总量和磁盘I/O是比较好的

7.数据类型约束

  • 约束是一种限制，他通过对表的行或者列的数据做出限制，来确保表数据的完整性和唯一性，在mysql当中一般有以下几种约束：

--
null 数据列可包含null值
not null 数据列不允许包含null值
default 默认值
primary key 主键(自动为not null值)（unique key + not null 自动被成primary key主键）
auto_increment 自动递增，适用于整数类型
unsigned 无符号
character set name 指定一个字符集

  • 当一张表里没有一个主键的时候，第一个出现的非空且唯一的列被视为有主键

7.1数据约束

//约束username不能为空
> create table test_null(id int,username varchar(20) not null) charset=utf8;
> insert into test_null values(1,'zhangsan')
​
//添加唯一约束
> alter table test_null add unique(id);
​
//主键
> create table user(id int primary key,name varchar(20),number int) charset=utf8;
​
//自增
> alter table user change id id int not null auto_increment;    //新增自增，主键任存在
​
//删除自增长
> alter table user change id id int not null;
​
//修改默认值
> alter table user alter number set default 0;
> desc user;
> insert into user values(1,'lisi');

7.2外键约束

  • 主要用于关联查询，外键约束要求数据表的数据引擎只能为InnoDB

查看当前mysql服务器支持的存储引擎

> show engines;

　　

修改数据表的默认存储引擎

# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
# systemctl restart mysqld
> create table teacher(id int primary key auto_increment,name varchar(20) not null) charset=utf8;
​
> create table student(id int primary key auto_increment,name varchar(20) not null,teacher_id int,foreign key (teacher_id) references teacher(id)) charset=utf8;
​
> insert into teacher(id,name) values(1,'zhangsan');
> insert into student(id,name,teacher_id) values(1,'zhangsan',1);

　　

8.mysql索引

  • 索引作为一种数据结构，其用途适用于提升检索数据的效率。

8.1MySQL索引的分类

  • 普通索引（INDEX）：索引列值可重复

  • 唯一索引（UNIQUE）：索引列值必须唯一，可以为null

  • 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为null，一个表只能有一个主键索引

  • 全文索引（FULL TEXT）：给每个字段创建索引

8.2MySQL不同类型索引用途和区别

  • 普通索引常用于过滤数据，如，以商品种类作为索引，检索种类为“手机”的商品

  • 唯一索引主要用于标识一列数据不允许重复的特征，相比主键索引不常用与检索的场景

  • 主键索引是行的唯一标识，因而其主要用途是检索特定数据。

  • 全文索引效率低，常用与文本中内容的检索。

8.3mysql使用索引

一、普通索引

//创建表添加索引
> create table student(id int not null,name varchar(50) not null,birthday date,sex char(1) not null,index sindex (name(50)));
//查看索引
> show index from student\G
​
//基于表结构创建索引
> create index tindex on strdent(id);
​
//修改表结构创建索引
> alter table student add index xindex(name(50));

　　

唯一索引

//创建表加唯一索引
> create table student3(id int not null,name varchar(50) not null,birthday date,sex char(1) not null,unique idname(id));
> show index from student3\G
​
//复制表结构(不会复制索引)
> create table student4 like student3;
> desc student4;
​
//添加唯一索引
> create unique index idindex on student4(id);

　　

主键索引

> create table student5(id int not null,name varchar(50) not null,birthday date,sex char(1) not null,primary key (id));
> show index from student5\G
​
> create table student6 like student3;
> alter table student6 add primary key (id);
> show index from student6\G
​
//删除索引
> alter table student drop primary key;

　　

删除索引

> drop index idindex on student;
> alter table student drop index idindex;

　　

8.4使用索引的原则

  • 常用于查询条件的字段较适合作为索引，例如WHERE语句和JOIN语句中出现的列

  • 唯一性太差的字段不适合作为索引，例如性别

  • 更新过于频繁（更新频率远高于检索频率）的字段不适合作为索引

  • 使用索引的好处是索引通过一定的算法建立了索引值与列值直接的联系，可以通过索引直接获取对 应的行数据，而无需进行全表搜索，因而加快了检索速度

  • 但由于索引也是一种数据结构，它需要占据额外的内存空间，并且读取索引也加会大IO资源的消 耗，因而索引并非越多越好，且对过小的表也没有添加索引的必要

目录

1.MySQL1.1安装MySQL2.结构化查询语言1.2数据定义语言DDL1.3数据操纵语言DML1.4事物控制语言TCL1.5数据查询语言DQL1.5.1排序查询order by子句1.5.2Distinct去重1.5.3分组查询Group by子句1.5.4分组查询添加条件Having子句1.5.51.5.6高级关联查询1.6数据控制语言DCL1.6.1创建用户1.6.2显示用户的权限1.6.3授权1.6.4删除用户3.实例3.1添加字段3.2表操作3.3操作表数据行3.4单表操作4.多表操作4.1一对多4.2多对多4.3一对一4.4多表联查5.数据导出方式6.数据类型6.1整型6.2 int(n)需要注意6.3浮点型6.4字符串6.5二进制数据（Blob）6.6日期时间类型6.7 枚举6.8MySQL数据类型使用建议6.9MySQL选择数据类型的基本原则7.数据类型约束7.1数据约束7.2外键约束8.mysql索引8.1MySQL索引的分类8.2MySQL不同类型索引用途和区别8.3mysql使用索引

1.MySQL1.1安装MySQL2.结构化查询语言1.2数据定义语言DDL1.3数据操纵语言DML1.4事物控制语言TCL1.5数据查询语言DQL1.5.1排序查询order by子句1.5.2Distinct去重1.5.3分组查询Group by子句1.5.4分组查询添加条件Having子句1.5.51.5.6高级关联查询1.6数据控制语言DCL1.6.1创建用户1.6.2显示用户的权限1.6.3授权1.6.4删除用户3.实例3.1添加字段3.2表操作3.3操作表数据行3.4单表操作4.多表操作4.1一对多4.2多对多4.3一对一4.4多表联查5.数据导出方式6.数据类型6.1整型6.2 int(n)需要注意6.3浮点型6.4字符串6.5二进制数据（Blob）6.6日期时间类型6.7 枚举6.8MySQL数据类型使用建议6.9MySQL选择数据类型的基本原则7.数据类型约束7.1数据约束7.2外键约束8.mysql索引8.1MySQL索引的分类8.2MySQL不同类型索引用途和区别8.3mysql使用索引

posted @ 2021-04-04 01:26  破碎的屋檐  阅读(105)  评论(0)  编辑  收藏  举报