mysql注入之前的mysql知识总结（根据《sql必知必会第4版》）

最新推荐文章于 2021-09-08 18:11:40 发布

冬瓜小暖

最新推荐文章于 2021-09-08 18:11:40 发布

阅读量248

点赞数

本文链接：https://blog.csdn.net/weixin_44237647/article/details/91912461

版权

工具：phpstudy的phpadmin
使用的数据库

----------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table creation scripts for IBM DB2.
----------------------------------------------


-------------------------
-- Create Customers table
-------------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  ,
  cust_city    char(50)  ,
  cust_state   char(5)   ,
  cust_zip     char(10)  ,
  cust_country char(50)  ,
  cust_contact char(50)  ,
  cust_email   char(254) 
);

--------------------------
-- Create OrderItems table
--------------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);

----------------------
-- Create Orders table
----------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date date     NOT NULL ,
  cust_id    char(10) NOT NULL 
);

------------------------
-- Create Products table
------------------------
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(254)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  varchar(1000) 
);

-----------------------
-- Create Vendors table
-----------------------
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) ,
  vend_city    char(50) ,
  vend_state   char(5)  ,
  vend_zip     char(10) ,
  vend_country char(50) 
);


----------------------
-- Define primary keys
----------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);


----------------------
-- Define foreign keys
----------------------
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrIt_Or FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrIt_Pr FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders
ADD CONSTRAINT FK_Or_Cu FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products
ADD CONSTRAINT FK_Pr_Ve FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table population scripts for IBM DB2.
------------------------------------------------


---------------------------
-- Populate Customers table
---------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-------------------------
-- Populate Vendors table
-------------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

--------------------------
-- Populate Products table
--------------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

------------------------
-- Populate Orders table
------------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2012-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2012-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2012-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2012-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2012-02-08', '1000000001');

----------------------------
-- Populate OrderItems table
----------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

在phpstudy中打开MySQL命令行新建一个数据库sql

create database sql;

然后将上述sql语句复制到数据库命令行
搭建好实验环境开始进行学习

主键
任意两行都不能有相同的主键值
每一行都必须有一个主键值（主键列不允许为NULL值）
主键列中的值不允许修改或更新
主键不能重用（如果某行从表中删除，他的主键不能赋给新的行）

SELECT DISTINCT vend_id, prod_price
FROM products
LIMIT 0 , 30
distinct作用于所有列

SELECT prod_name
FROM products
LIMIT 5
OFFSET 0 
SELECT prod_name
FROM products
LIMIT 0 , 5
这两个句子相同，注意limit是从0开始

SELECT prod_name-- aa
FROM products
LIMIT 0 , 5
--注释必须空格或+
#可以空格也可以不空格

SELECT prod_name
FROM products
ORDER BY prod_name
LIMIT 0 , 5
order by 和 limit 的次序
（limt是限制输出结果的，所以总是放在“最后”）意味着所有满足的条件中按照次序返回几项结果

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY 2 , 3
LIMIT 0 , 30
注意用数字的话，2，3值得是select 后的位置，而不是指的整个表
如果指定的是列名，则可以是未出现在select后的

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY 2 , vend_id
LIMIT 0 , 30
相对位置和实际位置可以混合使用

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY vend_id DESC , prod_price, prod_name
LIMIT 0 , 30
DESC降序排列直接作用于他之前的，默认排序为ASC（升序）

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 3.49
ORDER BY 1
LIMIT 0 , 5
where子句在order by子句后
<>与 !=在where子句中均表示不等于

SELECT prod_name, prod_price
FROM products
WHERE prod_price
BETWEEN 5
AND 10
LIMIT 0 , 5
between 低端值 and 高端值

SELECT prod_name, prod_price
FROM products
WHERE prod_price IS NULL 
is null 空值检查

select prod_name,prod_price 
from products
where prod_price >5 or prod_price <10
limit 0,5
or语句，当满足第一条时，无论后面是什么满足第一条的行均被检索出来

SELECT prod_name, prod_price
FROM products
WHERE prod_price >5
AND prod_price <10
and语句，必须所有条件都满足。

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01'
OR VEND_ID = 'BRS01'
AND PROD_PRICE >=10
LIMIT 0 , 30
AND操作符的优先级比OR高，所以这个句子表示把价格大于10且vend_id =‘BRS01’ 以及    vend_id =‘DLL01’的所有产品

SELECT prod_name, prod_price
FROM products
WHERE vend_id
IN (
'DLL01', 'BRS01'
)
AND PROD_PRICE >=10
in 与or 的相互联系  IN (   ) 代表  (vend_id ='DLL01' OR vend_id='BRS01')  
SELECT prod_name, prod_price
FROM products
WHERE NOT vend_id
IN (
'DLL01', 'BRS01'
)
AND NOT PROD_PRICE >=10
LIMIT 0 , 30
NOT运算符负责否定条件，（与in连用是时，比较方便）

SELECT prod_name
FROM products
WHERE prod_name LIKE 'f%y'
以F开头y结尾的产品名，如果是   'f%y%'   则代表y后可能还有空格
通配符搜索只能用于文本字段（字符串），非文本数据不能使用通配符
%     表示任意字符出现任意次
where prod_name like '%' 这个不能表示匹配产品名为NULL的行

SELECT concat( prod_id, vend_id, '(', prod_price, ')', '(', prod_name, ')' ) AS answer
FROM products
as是将他取一个别名，也可以concat(prod_id,vend_id) 反应出来的结果不带括号，如果是'(',prod_id,')' 则结果带个括号，不给prod_id加单引号，加了就当作文本处理了，而不是列了

SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num =20008
可以执行算术运算符号有   +，-，*，/

SELECT vend_name, upper( vend_name ) AS qq
FROM vendors
使用upper函数来把小写转换为大写

SELECT vend_name, length( vend_name ) AS qq
FROM vendors
使用length函数返回vend_name的长度

SELECT cust_name, cust_contact
FROM customers
WHERE soundex( cust_contact ) = soundex( 'Michael Green' ) 
soundex函数相当于模糊搜索，发音类似。

SELECT order_num
FROM orders
WHERE year( order_date ) =2012
使用year（）函数提取年份，order表中的order_date列的值的年份等于2012的order_num

数值处理函数
abs（）返回绝对值 sin（） cos（） tan（） 返回正弦余弦正切的值
pi（）返回圆周率 sqrt（）返回平方根 exp（）返回e的指数值

SELECT avg( prod_price ) AS avg
FROM products
AVG忽略列值为NULL的行

SELECT count( * )
FROM customers
count(*)不论列中包含的是NULL还是非空值

SELECT count( cust_id )
FROM customers
count(cust_id)对某列中具有值的行进行计数

SELECT max( cust_city )
FROM customers
如果是字符串数据则返回最大
如果是文本数据则返回最后一行（最后一行的任一值为NULL都算作NULL）
MAX()忽略NULL的行
MIN()同理！！！

SELECT sum( item_price * quantity ) AS qq
FROM orderitems
WHERE order_num =20005
SUM忽略NULL行 

SELECT avg( DISTINCT prod_price ) AS avg
FROM products
WHERE vend_id = 'DLL01'
使用distinct来排除相同的值
distinct只能使用列名不能使用count(*)

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

SELECT vend_id, count( * ) AS num
FROM products
GROUP BY vend_id
group by 根据vend_id 进行分组，对每个组进行 vend_id和count(*)

SELECT cust_email, count( * ) AS num
FROM customers
GROUP BY cust_email
分组列中如果有NULL值，则将作为一个分组返回，如果列中有多行NULL值，将他们分为一组

SELECT vend_id, count( * ) , prod_price AS num
FROM products
GROUP BY vend_id, prod_price
现根据vend_id 分组，再根据prod_price分组然后返回上述结果，属于嵌套分组

SELECT vend_id AS c, concat( prod_price, count( * ) ) AS b, prod_price AS num
FROM products
GROUP BY num, c
可以根据之前取过的别名进行group by

SELECT cust_id, count( * ) AS orders
FROM orders
GROUP BY cust_id
where过滤的是行，而having过滤的是分组
也可以说having是在分组后进行过滤，即就是having过滤的是分组聚集值

select vend_id ,count(*) as num from products where prod_price>=4 
group by vend_id  
having count(*) >=3
先是where过滤数据，下来是having进行分组过滤

SELECT order_num, count( * ) AS qq
FROM orderitems
GROUP BY order_num
HAVING count( * ) >=3
ORDER BY qq, item_price
使用order by 对输出结果进行排序最后的item_price 没有出现过，所以按照符合的分组的第一行进行大小排序

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id
IN (

SELECT cust_id
FROM orders
WHERE order_num
IN (

SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01'
)
)
例子中不同的 信息存储在不同的表中，检索出的信息作为上一个检索的条件
select 子查询可以把不同表中的数据作为索引条件，与IN连用
子查询是在4.1版本后引入的，子查询的select语句只能查询单个列

SELECT cust_name, cust_state, cust_id, (

SELECT count( * )
FROM orders
WHERE orders.cust_id = customers.cust_id
) AS qq
FROM customers

SELECT order_num, (

SELECT count( * )
FROM customers
WHERE orders.cust_id = customers.cust_id
) AS qq
FROM orders
子句查询的where不能和主句的where是一个表
orders.cust_id与customers.cust_id叫完全限定列名
整个句子一共执行5遍
计算字段使用子查询

SELECT order_num, (

SELECT count( * )
FROM customers, orders
WHERE orders.cust_id = customers.cust_id
) AS qq
FROM orders
联结查询将两个表的cust_id联结起来

SELECT vend_name, prod_name, prod_price, vend_id
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
因为from后有两个表，所以前面vend_id必须用完全限定列名，否则会报错

SELECT vend_name, prod_name, prod_price
FROM vendors, products
如果联结的没有相关性，则就按照顺序输出（叉联结）（由没有联结条件的表关系返回的结果为笛卡儿积）

select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id
返回的值如上（多了一个where条件）

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num =20007
联结多个表，联结也可以代替子查询，子查询不一定是最有效方法

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE C.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'RGAN01'
给表起别名简化语句

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers AS c1, customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones'
customers表中的cust_contact 为Jim Jones的信息 这种联结称为自联结

SELECT c . * , o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'RGAN01'
自然联结派出多次出现，使每一列只返回一次，你只能选择那些唯一的列

内联结与外联结
SELECT customers.cust_id, orders.order_num
FROM orders
INNER JOIN customers ON customers.cust_id = orders.cust_id
只返回两个表的公共处
SELECT customers.cust_id, orders.order_num
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id
左联结
按照左表进行返回，比如左表某个cust_id值没有联结到右表，则返回的为
cust_id1000002
order_num NULL
右联结与之同理
带有聚集函数的联结
SELECT customers.cust_id, count( orders.order_num ) AS numid
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id
先关联，再分组，最后输出

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state
IN (
'IL', 'IN', 'MI'
)
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4ALL'
联合查询 返回两个select查询的结果，并且自动过滤重复的结果，如果不愿意，则可以union all
union注意事项
1union必须由大于等于2的select语句组成，语句之间用union隔开
2union的每个查询必须包含相同的列，表达式或聚集函数（列的次序可以不同）
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state
IN (
'IL', 'IN', 'MI'
)
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4ALL'

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state
IN (
'IL', 'IN', 'MI'
)
UNION ALL
SELECT cust_email, cust_contact, cust_name
FROM customers
WHERE cust_name = 'Fun4ALL'
如这两段代码的列的顺序不同，则会第一个select检索出来的按照第一个次序排列，但第二个select检索出的数据按找第二个次序排列，列名仍按照第一个次序
3列数据类型必须兼容

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state
IN (
'IL', 'IN', 'MI'
)
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4ALL'
ORDER BY cust_name, cust_contact
order by只对结果排序，所以union查询只对最后输出结果排序，即只能有一个order by

insert into customers values ('1000000006', 
'Toy Land',
 '123 Any Street', 
'New York', 
'NY',
 '11111',
'USA',
 NULL, 
NULL)
按照一定次序，带单引号，每一列必须提供一个值，
但是如果表的列结构改动可能会引起混乱
insert需要足够权限

insert into customers 
(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
values ('1000000006', 
'Toy Land',
 '123 Any Street', 
'New York', 
'NY',
 '11111',
'USA')
指定相应的列名与值，避免了混乱，也可以进行部分插入操作，如果没有插入的值，
当该列允许为NULL或无值 or 当该表定义中给出的默认值  才可以省略
（具体根据DBMS的配置）

insert into customers 
(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
select cust_id,
cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew
把custnew表中检索到数据的插入customers中，这个对应关系是行数的对应
也可以在select句中加入where过滤要插入的数据

create table custcopy as
select * from customers
将表的内容复制到一个新建表上
1select 任何选项和子句都可以使用
2可利用联结从多个表插入数据
3数据只能插入到一个表中

update语句需要足够权限
UPDATE customers 
SET
 cust_email = 'kim@thetoystore.com' 
 cust_email='sam@toyland.com' 
WHERE cust_id = '1000000005'
只需要设置一个set
cus_email = NULL 来除去列中的值（即删除某一列）
cust_email=' ' 空字符串

删除数据delete需要足够权限,所有的删除都需要确认
DELETE FROM customers
WHERE cust_id = '1000000006'
删除指定的一行
delete不需要列名和通配符，用where控制，如果删除的数据有外键控制，（书上说会报错，但是没操作没报错）
truncate table custcopy 直接释放数据页（数据全部清除，保留表单）

CREAte table products1
(
prod_id char(10) not null,
vend_id char(10) not null,
prod_name char(10) not null,
prod_price decimal(8,2) not null,
prod_desc varchar(1000) null
)
列名，数据类型，是否为空 用空格分开，每列用逗号隔开。
关于NULL值的使用
允许NULL值的列也允许插入是不给出该列的值
不允许NULL值的列不接受没有列值的行

insert into products1 (vend_id)
values (NULL)
会报错   Column 'vend_id' cannot be null


CREAte table products1
(
prod_id char(10) not null  default 1,
vend_id char(10) not null,
prod_name char(10) not null,
prod_price decimal(8,2) not null,
prod_desc varchar(1000) null ,
primary key (prod_id)
)
创立表之后需要插入数据，如果未插入则prod_id 的值取1,并且定义主键 

ALTER TABLE vendors ADD vend_phone3 char( 20 ) 
增加一列，后面还可写NULL/NOT NULL 或者default 1
但是不能直接赋值 ，例如‘x'

drop table custcopy
删除custcopy表

rename table custcopy to custcopy1
重命名表名

ALTER TABLE vendors CHANGE COLUMN hhh aaa varchar( 20 ) 
重命名列名 后面要加数据类型

笔记同时也上传到我的资源中
希望能对mysql知识欠缺的朋友一些帮助

冬瓜小暖

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql注入之前的mysql知识总结（根据《sql必知必会第4版》）

工具：phpstudy的phpadmin使用的数据库------------------------------------------------ Sams Teach Yourself SQL in 10 Minutes-- http://forta.com/books/0672336073/-- Example table creation scripts for IBM DB2....
复制链接

扫一扫