mysql数据库的基础操作

mysql的四种语句

sql语言是关系数据库的标准语言用于维护和管理数据库。

DDL语句:数据定义语言，创建库，表，索引等。
DML语句:数据操纵语言，在表中插入新数据，更新原有数据，删除不需要的数据。
DQL语句:数据查询语言，查询表中数据的记录。
DCL语句:数据控制语言，设置或更改登入数据库的用户的权限。

创建库与表

使用caeate命令创建库和表

caeate database user;	#user为数据库名

ok表示成功

查看数据库

创建表要先进入数据库才能创建

use user;	#进入数据库
#创建表按照	create table 表名 (字段1 数据类型,字段2 数据类型,......);的格式创建，可以在数据类型后指定该字段的默认值或是否为空等，例如:id int not null	id int default' '

create table names (id int,name char(10),passwd varchar(11));

#可以在表名前加上if not exists 表示如果没有这张表就创建，有了则不执行
create table if not exists names (id int,name char(10),passwd varchar(11));

查看表

查看表结构

drop database user;	#删除数据库
drop tables names;	#删除表
drop table user.names	#第二种删除表的方式，这种方式不需要进入数据库

常用的数据类型

int：整数类型
float：单浮点类型，精确到小数点后六位
double：双精度浮点类型，精确到小数点后十二位
char：固定长度的字符类型，不足固定长度会在前面补零，超过长度会进行四舍五入
varchar：可变长字符类型，相当于设置字符上限
text：文本类型
image：图片类型
decimal(4，3)：数字类型，小数点前四位，小数点后三位

增、删、改、查

增

使用insert命令在表中添加记录

#在表中增加记录，如果增加的字段不是数字，那么需要加上''
#增加记录的两种方式

#insert into 表名(字段1，字段2，字段3) values(字段1的值，字段2的值，字段3的值)
#insert into 表名 values(字段1的值，字段2的值，字段3的值)

insert into names(id,name,passwd) values(1,'useryi','abc123')
insert into names values(1,'useryi','abc123');

查

使用select命令查看表中数据

select * from names;	#查看该表的所有数据
select id,name from names;	#查看该表的id,name字段的数据
select id,name from names where id=1;	#查看id字段的值为1的记录的id,name字段的数据

查看之前创建的names表中的数据

改

使用alter修改表名和表结构

#修改表名alter table 旧表名 rename 新表名;
alter table names rename name;

#修改表中字段名，修改数据类型要确保字段的值符合新的数据类型
alter table names change passwd password varchar(10);

#在表中增加字段
alter table names add address varchar(50);

查看修改后的表

#表中删除字段
alter table names drop address;

删除address后查看表

删

有三种方法删除表
一：delect

delete from names;

这种删除方式以行的方式删除，会保留表结构，每删除一行都会记录在日志中可以回滚，可以带上where进行判断，速度比较慢。
二：drop

drop table names;

整表删除，无法跟whrer进行判断，删除不记录在日志中，不可回滚，速度最快
三：truncate

truncate table names;

整表删除，删除后创建同一张表，无法跟whrer进行判断，删除不记录在日志中，不可回滚，速度比较快

创建用户、为用户赋予权限

查看mysql用户的用户信息

#先进入mysql这个库中，然后查看user表，由于user表字段很多，建议选取其中部分字段进行查看
use mysql;
select user form user;

查看用户

#创建登录用户,%表示所有
#格式：create user '用户名'@'登入的ip地址' identified by '密码';
create user 'list'@'%' identified by '123456';

#给用户重命名
rename user 'list'@'%' to 'zhangsan'@'%'

#删除用户
drop user 'zhangsan'@'%'

#修改密码，修改其他用户的密码
set password = password('abc123');
set password for 'list'@'%' = password('abcabc');

赋予权限

grant 权限列表 on 数据库名.表名 to ‘用户名’@‘来源地址’ identified by ‘密码’;
权限列表：表示授权使用各种操作命令，用逗号分隔，如：select，update，可以直接使用all表示所有权限
grant all on *.* :表示所有表所有权限

grant all on *.* to 'list'@'%' identified by '123456';

#查看权限
show grants for 'list'@'%';

#撤销权限
revoke all on *.* from 'list'@'%';

usage是用户的登录权限，该权限消失则用户消失。

约束

1.约束保证数据的完整性和一致性。
2.约束分为表级约束和列级约束。
3.约束类型包括：
主键约束：该字段值唯一且非空。
外键约束：某一张表中的主键字段在第二张表中不是主键，则该字段为第二张表的外键。
非空约束：该字段值不能为空。
唯一性约束：该字段值唯一可以为空，但是空值只能有一次。
默认值约束：该字段默认值为xxx。
自增约束：该字段值每一行+1。

#创建主表，为主表添加主键
mysql> create table b (id int(4),name varchar(50));
Query OK, 0 rows affected (0.02 sec)
mysql> alter table b add constraint PK_id primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 为从表添加外键，并将从表的id字段和主表的id字段建立外键关联
mysql> create table c (id int(4),name varchar(50) primary key,age int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> alter table c add constraint FK_id foreign key (id) references b (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引的介绍

索引是一个排序的列表，存储索引值和这个值所对应的物理地址。通过索引，对表进行查询无需对整个表进行扫描，通过物理地址就能找到所需数据。在myisam引擎中，索引单独存放于索引表中，所以需要额外的磁盘存放索引表。在innodb引擎中，会将表中主键作为索引，不需要额外磁盘空间存储索引。

myisam引擎和innodb引擎存储索引的区别类似于书本的目录和书签。

索引的作用

1、在数据库中利用合理的创建和使用索引能够大大加快查询速率。

2、尤其是表很大或查询涉及到多张表时，可以极大的提升查询速度。

3、降低数据库的io成本和数据库的排序成本。

4、通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

创建索引的原则

索引虽然可以提升数据库查询的速度，但是索引并不是想创建就创建的，由于mysql数据库查询会先遍历索引表再遍历数据表，所索引创建不当也会降低查询速度。

索引创建的原则：
1、表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。

2、记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。

3、经常与其他表进行连接的表，在连接字段上应该建立索引。

4、唯一性太差的字段不适合建立索引。

5、更新太频繁地字段不适合创建索引。

6、经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。

7、索引应该建在选择性高的字段上。

8、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

索引的分类

普通索引

最基本的索引类型，没有唯一性之类的限制。

#直接创建索引
CREATE INDEX 索引名 ON 表名 (字段);
举例:create index phone_index on member (phone);

#修改表结构的方式创建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
举例:alter table member add index id_index (id);

#创建表的时候指定索引
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型,[...],INDEX 索引名 (列名));

唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

#直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);举例:create unique index address_index on member (address);


#修改表方式创建
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
举例:alter table member add unique cardid_index (cardid);


#创建表的时候指定
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型，[...],UNIQUE 索引名 (列名));
举例:create table amd2 (id int,name varchar(20),unique id_index (id));
show creat table amd2;

主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。
一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

#创建表的时候指定
CREATE TABLE 表名 ([...],(列名) PRIMARY KEY);
例：create table test1 (id int primary key,name varchar(20));

#修改表方式创建
ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

组合索引

可以是单列上创建的索引，也可以是在多列上创建的索引。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

例:create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));

全局索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

#直接创建索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
举例：select * from member;
create fulltext index remark_index on member (remark);

#修改表方式创建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

#创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

#使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');
例：select * from member where match(remark) against('this is vip'); 
select * from member where remark='this is vip';