您现在的位置是：首页 >其他 >SQL语句网站首页其他

创建及删除数据库和表
 CREATE DATABASE 数据库名;
 CREATE DATABASE school;
 创建新的表
 CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]);
  #主键一般选择能代表唯一性的字段，不允许取空值(NULL)，值也不允许重复，主键字段的值是唯一的。一个表只能有一个主键。
USE school;                  #切换school库
 CREATE TABLE class01 (
id int NOT NULL,
name char(10) NOT NULL,
score decimal (5,2),
passwd char(48) DEFAULT '',
PRIMARY KEY (id));        #新建表class01
 
删除指定的数据表
 DROP TABLE [数据库名.]表名;
USE school
 DROP TABLE class01;

删除指定的数据库
 DROP DATABASE 数据库名;
 DROP DATABASE school;

管理表中的数据记录 表数据的增删改查
向数据表中插入新的数据记录（增）
 方法一：为所有字段插入值
 insert into 表名 values (所有字段的值);   #每个字段值用逗号相隔；
 方法二：为指定字段插入值
 INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值，...);    #注意字段的属性not null，则必须为该字段插入值
#为表中所有字段插入值，此种方式密码会以明文显示。
 INSERT INTO class01 VALUES (2, '张三', 90.5, 654321);
 #为指定字段插入值
 INSERT INTO class01 (id, name, passwd) values (1, '李四', PASSWORD('123456'));
 #PASSWORD ('123456')：查询数据记录时，密码字串以加密形式显示。若不使用PASSWORD()，查询时以明文显示。.
 #下面这条命令不生效，因为name字段设置了not null，不允许为空值。
 INSERT INTO class01 (id, score, passwd) values (3, 88, 123123);
 SELECT * FROM class01;    #查询表的数据记录

查询表数据记录（查）
SELECT * FROM 表名;      #查询表中所有记录
 ​
 #按条件查找数据记录，且只显示指定字段的值
 SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
 ​
 #表中的记录默认是横向展示的，当字段很多时显示很乱，可以将";"换成"G"，改成纵向展示每条记录。
 select * from 表名G

#mysql的分页语句：
 select * from 表名 limit 2; 
 select * from 表名 limit 2,3;       #显示第2行后的前3行（即显示第3~5行）
 select * from 表名 limit 19,11;     #查看第20行到第30行的记录
 select * from 表名 id>=10 and id<=20;    #查看id值在10到20之间的数据记录
修改/更新数据表中的数据记录（改）
修改字段一般要加上条件，不然会把所有行都修改了。
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
UPDATE class01 SET passwd= PASSWORD(' ') WHERE name='张三';
 #修改name为“张三”的数据记录，将其密码修改为以密文显示。
 
 UPDATE class01 SET score=77,passwd=' ' WHERE id=1;
 #修改id为1的数据记录，将score字段值改为77，passwd字段改为无值。

在数据表中删除指定的数据记录（删）
DELETE FROM 表名 [WHERE 条件表达式];     #删除一定要加条件，不然会删除整个表
 例:
 DELETE FROM class01 WHERE id=2;     #删除id字段值为2的数据记录

修改表名和表结构
修改表名 rename
ALTER TABLE 旧表名 RENAME 新表名
 例:
 ALTER TABLE class01 RENAME tt01;   #将class01的表名修改为tt01
 ALTER TABLE tt01 RENAME class01;   #将tt01的表名修改回class01

扩展表结构（增加字段） add
ALTER TABLE 表名 ADD 字段名 数据类型;
 例：
 ALTER TABLE class01 ADD address varchar(50) not null default '地址不详';
 #增加“address"字段，不允许为空值，默认值为“地址不详”。
 #default '地址不详'：表示此字段设置默认值为"地址不详"，可与NOT NULL配合使用。

 修改字段名，添加唯一健 change
CHANGE可修改字段名、数据类型、约束等所有项。
 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 [数据类型] [约束];
 例:
 ALTER TABLE class01 CHANGE score phone int unique key;
 #将score字段名修改为phone，数据类型修改为int整数型，并添加唯一键约束。

删除字段 drop
 ALTER TABLE 表名 DROP 字段名;
 例: ALTER TABLE class01 DROP passwd;     #删除passwd字段

添加主键约束 add primary key
创建表时设置主键
create table 表名(字段1 XXX, 字段2 XXX, ....primary key(字段));
create table 表名(字段1 XXX primary key, . . ..);     #将主键作为字段1的属性

在现有表中添加主键
 ALTER TABLE 表名 add primary key(字段名);
 例:
 ALTER TABLE class01 add primary key(id);     #将id字段添加为主键

主键primary key 和 唯一键unique key：
共同点：字段的值都是唯一的，不允许有重复值。
不同点：
自定义的表中只能有一个主键，但是可以有多个唯一键。
主键字段中不允许有null值，唯一键允许有null值。
（系统的表中可以有多个主键）

MYSQL常见的约束
主键约束（primary key） PK
自增长约束（auto_increment）
非空约束（not null）
唯一性约束（unique）
默认值约束（default）
零填充约束（zerofill）
外键约束（foreign key）FK

use school;          #切换到school库
 ​
 create table if not exists tt01 (                 #创建表tt01
 id int(4) zerofill primary key auto_increment,    #对id字段设置零填充约束、主键约束、自增长约束
 name varchar(10) not null default '匿名',         #对name字段设置非空约束、默认值约束
 cardid int(18) not null unique key,               #对cardid字段设置非空约束、唯一键约束
 hobby varchar(50));
 ​
 if not exists:
 表示检测要创建的表是否已存在，如果不存在就继续创建。如果存在就忽略，不进行创建。
 int(4) zerofill：
 零填充约束，表示若数值不满4位数，则前面用“0”填充到4位，例0001，0012。
 #int(11) zerofill：表示若数值不满11位数，则前面用“0”填充到11位，例如00000001234。
 primary key：
 表示此字段有主键约束，此字段数据不可以重复且不允许为NULL，一张表中只能有一个主键。
 auto_increment: 
 表示此字段为自增长字段，即每条记录自动递增1。
 一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复。
 如果不指定则默认从1开始递增；
 自增长字段数据不可以重复；
 自增长字段必须是主键；
 如果添加的记录数据没有指定此字段的值，那么添加失败也会自动递增一次。                    果添加的记录数据没有指定此字段的值，那么添加失败也会自动递增一次
 auto_increment约束的字段只能是整数型。
 unique key：
 表示此字段有唯一键约束，此字段数据不可以重复；一张表中只能有一个主键，但是一张表中可以有多个唯一键。
 not null：
 表示此字段不允许为NULL。
 default '匿名'：
 表示该字段的默认值为“匿名”。

总结
查看数据库和表：
show databases;
use 库名;
show tables;
desc 表名;
desc 表名G
show create table 表名;
创建及删除数据库和表：
create database 库名;
create table 表名(字段1 数据类型 [属性], 字段2 .....);
DROP TABLE [数据库名.]表名;
DROP DATABASE 数据库名;
表数据的增删改查：
insert into 表名 values (所有字段的值);
insert into 表名 (指定字段名称) values(字段的值);
delete from 表名 [where ...];
truncate table 表名;       #删除表中所有数据。相当于格式化，速度很快
update 表名 set 字段=值[, 字段2=....] [where ...];
select 字段1[,字段2....] from 表名 [where ...];
select * from 表名;
修改表名和表结构：
alter table 表名 RENAME/ADD/CHANGE/DROP 字段名 数据类型 属性;
alter table 表名 ADD 键名(字段); //给指定字段添加键约束
注：零填充约束、非空约束、默认约束、自增长约束不能通过ADD方式添加，可以在建表时设置，或者CHANGE修改字段时添加。
主键和唯一键：
主键：primary key
唯一键：unique key
共同点： 字段的值都是唯一性，不允许有重复的值
不同点：
一个表中 只能有1个主键，但是可以有多个唯一键
主键字段中不允许有null值，唯一键是允许有null
添加主键字段的三种方法：
创建表时指定主键字段的两种方式：
create table 表名 (字段1 XXX,字段2 xxx,...,primary key(字段));
create table 表名 (字段1 XXX primary key, ....);
在现有表中添加主键：
alter table 表名 ADD primary key(字段);

MySQL数据表的高级操作
克隆表，将数据表的数据记录生成到新的表中
create table 新表名 like 旧表名;             #通过LIKE方法，复制旧表的结构生成新表
insert into 新表名 select * from 旧表名;     #再将旧表数据导入新表

创建新表的同时，导入旧表数据
 create table 新表名 (select * from 旧表名);

查看表结构的三种方法
方法一：
 desc 表名;      #以表格形式显示表结构
 方法二：
 show create table 表名;     #以创建表的命令形式显示表结构
 方法三：
 desc 表名G       #如果字段较多，也可以使用"desc 表名G"的方式竖向显示表结构

清空表，删除表内的所有数据
delete                               返回的结果内有删除的记录条目；有自增长字段新添加的记录会从原来最大的记录ID后面继续自增写入记录。
delete from 表名;
 #DELETE清空表后，返回的结果内有删除的记录条目；
 DELETE 工作时是一行一行的删除记录数据的，删除时速度较慢；
 如果表中有自增长字段，使用"DELETE FROM"删除所有记录后，新添加的记录会从原来最大的记录ID后面继续自增写入记录。
 ​
truncate              格式化
truncate table 表名;    #相当于格式化，速度很快
 #TRUNCATE清空表后，没有返回被删除的条目。
 TRUNCATE 工作时是将表结构按原样重新建立，因此在速度方面 TRUNCATE 会比DELETE清空表快。
 使用TRUNCATE TABLE清空表内数据后，ID会从1开始重新记录。

创建临时表 TEMPORARY        在连接退出后被销毁，用"show tables"查看不到临时表，用于测试使用，
临时表创建成功之后，使用"SHOW TABLES"命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以执行增删改查等操作，比如使用"DROP TABLE"语句手动直接删除临时表。
临时表一般用于测试使用，只有当前连接的用户才能看到，相同用户换个终端登录也看不到。退出当前连接（退出数据库）之后临时表会被销毁。
CREATE TEMPORARY TABLE 表名(字段1 数据类型,字段2 数据类型[, ...] [, PRIMARY KEY (主键名)]);
复

创建外键约束，保证数据的完整性和一致性
外键的定义： 如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
外键的作用：
为了一张表记录的数据不要太过冗余。
保持数据的一致性、完整性。
主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）
注意：
与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
添加外键时必须关联主键（即主表先要添加主键，之后从表再添加外键）
#创建主表class
 create table class (cid int,cname varchar(10));
 #创建从表student
 create table student (id int,name varchar(10),age int,classid int)
 #为主表class的cid字段添加一个主键约束。constraint为主键创建别名，主键名建议以“PK_”开头。
 alter table class ADD constraint PK_CID primary key (cid);
 #为从表student的classid字段添加外键，并将student 表的classid字段和class 表的cid字段建立外键关联。外键名建议以"FK_”开头。
 #references关联主键表中的字段。
 alter table student ADD constraint FK_CLASSID foreign key (classid) references class(cid);
 #添加外键时必须关联主键（即先要添加主键，再添加外键）
 #添加主键和外键时，即使不设置别名，系统也会自动创建一个别名。

为两个表插入数据：
 INSERT INTO class values(1,'钢琴');          #为主表插入数据
 INSERT INTO student values(1,'张三',18,1);   #为从表插入数据
 INSERT INTO student values(2,'李四',18,2);   #这条数据会插入失败，因为主表的cid字段没有为2的值

删除主键表中的数据记录，添加外键表中数据需要主键表中有对应字段记录
如果想删除主键表中的某条记录，必须先删除外键表中相关联的字段记录
 #删除数据记录时，要先删从表再删主表。也就是说删除主键表的记录时，必须先删除其他与之关联的表中的记录。
 delete from student where classid=1;       //先删从表的数据
 delete from class where cid=1;             //再删主表的数据

查看和删除外键约束
第一步删除外键约束，第二步删除别名。
#查看外键约束
 show create table student;
 desc student;
 #删除外键约束。
 alter table student drop foreign key FK_CLASSID;   //先删除外键约束
 alter table student drop key FK_CLASSID;           //再删除键（即删除别名）

数据库用户管理
新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
'用户名'： 指定将创建的用户名。
'来源地址'： 指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost， 允许任意主机登录可用通配符%
'密码'：
若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加PASSWORD '密文'；
若省略"IDENTIFIED BY" 部分，则用户的密码将为空(不建议使用)
使用明文密码创建用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123123';
使用密文密码创建用户
 SELECT PASSWORD('123456');    #先获取密文
 CREATE USER 'lisi'@'localhost' IDENTIFIED BY PASSWORD '密文';
查看用户信息
创建后的用户保存在 mysql 数据库的 user 表里
 USE mysql;    #切换到mysql库
 SELECT User,authentication_string,Host from user;  #查看user表中的用户信息
重命名用户 rename
将用户zhangsan改名为wangwu ：
 RENAME USER 'zhangsan'@'localhost' TO 'wangwu'@'localhost';
删除用户信息 drop
 DROP USER 'lisi'@'localhost';
 修改当前登录用户的密码
set password = password('123456');
修改其他用户的密码
 set password for 'nancy'@'localhost' = password('abc123');
忘记root用户密码的解决办法
#修改配置文件/etc/my.cnf，不使用密码直接登录到mysql。
vim /etc/my.cnf
[mysqld]下添加:
skip-grant-tables          #添加此行，使登录mysql不使用授权表
service mysqld restart     #重启服务
mysql    #直接登录，无需密码
#使用update命令修改root密码，刷新数据库。
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('123456') where user='root';
flush privileges;   #刷新数据库
quit                #退出
mysql -uroot -p123456      #免交互登录的话，"-p密码"不能有空格
查看当前登录用户
select user ();    #查看当前登录用户和来源地址
数据库用户授权
授予用户权限
GRANT语句：专门用来设置数据库用户的访问权限。
当指定的用户名不存在时，GRANT语句将会创建新的用户；
当指定的用户名存在时，GRANT 语句用于修改用户信息。
 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
 权限列表：
 用于列出授权使用的各种数据库操作，以逗号进行分隔如"select, insert,update"。
 使用"all"表示所有权限（实际上部分权限仍无法使用，只包括大部分权限），可授权执行任何操作。
 数据库名.表名：
 用于指定授权操作的数据库和表的名称，其中可以使用通配符*。
 例如，使用"mysql.*" 表示授权操作的对象为mysql数据库中的所有表。
 '用户名'@'来源地址'：
 用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。
 来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.tt.com"、 “192.168.80.8"等。
 IDENTIFIED BY：
 用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略"IDENTIFIED BY"部分，则用户的密码将为空。

允许用户 zhangsan 在本地查询school数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。使用密码“123456”进行登录。
 GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';
使用zhangsan 用户在本地登录查看：
mysql -u zhangsan -p123456     #免交互登录时，"-p密码"不能有空格
允许用户 nancy 在所有终端远程连接mysql，并拥有所有权限。使用密码“123456”进行登录
GRANT ALL PRIVILEGES ON *.* TO 'nancy'@'%' IDENTIFIED BY '123456';
使用nancy用户远程连接mysql：
 mysql -unancy -p123456 -h 192.168.72.50 -P3306
#-p123456，小写p指定登录密码
 #-P3306，大写P指定端口号
 #-h 192.168.72.50，指定目标IP/主机名

查看用户的权限
SHOW GRANTS;     #查看当前用户（自己）的权限
 SHOW GRANTS FOR 用户名@来源地址;    #查看其他用户的权限
 show grants for 'zhangsan'@'localhost';

 撤销用户的权限
 revoke 权限列表/ALL on 库名.表名 from '用户名'@'来源地址';
针对用户 nancy@%，撤销其除了登录权限外的其他权限。
revoke all on *.* from 'nancy'@%;

总结
用户管理
create user 'username'@'address' identified by 'password';   #创建用户
 select user,host,authentication_string from mysql.user;      #查看用户信息
 rename user old_user to new_user;        #修改用户名
 drop user '用户名'@'来源地址'；            #删除用户
 set password = password('XXXX');         #修改当前登录用户的密码
 set password for '用户名'@'来源地址' = password('XXXX');     #修改其他用户的密码
 select user ();      #查看当前登录用户和来源地址
 ###忘记root用户密码的解决方法：
 修改mysql配置文件/etc/my.cnf，在 [mysqld] 配置项下加入 skip-grant-tables
 service mysqld restart   #重启服务
 mysql    #免密登陆  
 update mysql.user set authentication_string=password('XXX') where user='root';  #修改密码
 flush privileges;    #刷新数据库

用户授权
#授予用户权限
 grant 权限列表/ALL ON 库名.表名 to 'username'@'address' [identified by 'XXXXX'];
 show grants;                               #查看当前用户（自己）的权限                 
 show grants for 'username'@'address';      #查看其他用户的权限
 revoke 权限列表/ALL on 库名.表名 from 'username'@'address';    #撤销用户的权限
 mysql -u 用户名 -p[密码] -h 目标IP/主机名 -P 端口号              #远程连接mysql

MySQL数据库之索引
索引的概念
索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。
使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
索引是表中一列或者若干列值排序的方法。
建立索引的目的是加快对表中记录的查找或排序。（加快查询速度、对字段值进行排序）
索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。
使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
索引是表中一列或者若干列值排序的方法。
建立索引的目的是加快对表中记录的查找或排序。（加快查询速度、对字段值进行排序）
索引的作用
优点
设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
可以降低数据库的I/O成本，并且索引还可以降低数据库的排序成本。
通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
可以加快表与表之间的连接。
使用分组和排序时，可大大减少分组和排序的时间。
建立索引在搜索和恢复数据库中的数据时能显著提高性能。
缺点
索引需要占用额外的磁盘空间。
对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
而 InnoDB 引擎的表数据文件本身就是索引文件。（索引文件和数据文件是同一个）
在插入和修改数据时要花费更多的时间、消耗更多性能，因为索引也要随之变动。

创建索引的原则依据
当一个表写入多、读取很少的时候，不需要建立索引。
唯一性太差的字段、更新太频繁地字段、大字段，不适合做索引

索引的分类和创建
先创建一个数据表member
 create table member(id int(10) ,name varchar(10) ,cardid int(18) ,phone int(11) ,address varchar(50),remark text);
普通索引
普通索引是最基本的索引类型，没有唯一性之类的限制。
直接创建索引
 CREATE INDEX 索引名 ON 表名 (列名(length));