SQL语句 3

insert 插入数据

1. 单条数据

INSERT INTO 
items(name,city,price,number,picture)  
VALUES
('耐克运动鞋','广州',500,1000,'003.jpg');

1. 多条数据

INSERT INTO 
items(name,city,price,number,picture) 
VALUES
('耐克运动鞋','广州',500,1000,'003.jpg'),
('耐克运动鞋2','广州2',500,1000,'002.jpg');

update 更新替换

update 表名 set 字段A = replace(字段A ,"png","jpg" );
update 表名 set 字段A = CONCAT( 字段A  , "xxx"  );
update 表名 set 字段A=SUBSTR(字段A,4);    扔掉开始的4个
update 表名 set 字段A=SUBSTR(字段A,-3);   保留结尾的3个

delete 删除表中的行

DELETE FROM 表名称 WHERE 列名称 = 值
DELETE 别名 FROM 表名称 别名 WHERE 列名称 = 值

DELETE FROM user WHERE name = 'Wilson'
DELETE t FROM user t WHERE name = 'Wilson'

第一阶段 扩充

连接MySQL数据库服务器

1、连接本地数据库（“-p”和“123456” 之间不能有空格）

>mysql -h localhost -u root -p123456

>mysql -h localhost -u root -p（按回车）
>Enter password:**            隐藏密码

>mysql -h localhost -u root -p123456 -D testdb
							  指定数据库 testdb

2、连接远程数据库（192.168.1.2）端口“3306”

>mysql -h 192.168.1.2 -P 3306 -u root -p123456

3、MySQL的操作管理界面

show databases；  查看 数据库列表
create database Mytest;  创建数据库
select database();   显示当前数据库 
use test；        进入 test数据库
show tables;      查看test数据库中的 所有表

把一个DataBase比作一栋房 一个Schema比作这栋房里的一间房，每间房有自己的构造 一个user比作房间拥有者，拥有Schema的权限，你想在自己的房间里怎么布置，都行 房间的布置，就以table为单位，鞋柜可以是一个table，衣柜可以是一个table 鞋有不同的类型，衣服有不同的类型，对应数据库的数据类型

定义：坐火车 = 买票 +上车 这里的坐火车就是一个事务，还有一种情况就是买票成功了，但是没有赶上火车，这时候我就需要把票给退了，退票这个操作就称为回滚

事务

start tran：设置起点,开始事务
commit tran：事务的提交，事务执行完毕
rollback tran：是进行事务回滚
save tran：创建书签，只允许部分回滚

begin tran tran_AddUserInfo --开始事务
declare @tran_error int;
set @tran_error=0;
begin try
  insert into dbo.UserInfo values(2016009,'aaa','2016-08-19 09:13:41.227','男')
  insert into dbo.UserInfo values(2016009,'bbb','2016-08-19 09:13:41.227','哼哼哼')
  insert into dbo.UserInfo values(2016009,'ccc','2016-08-19 09:13:41.227','哈哈哈')
end try
begin catch
  set @tran_error=@tran_error+1; --加分号或不加都能正常执行
end catch
if(@tran_error>0)
begin
  rollback tran tran_AddUserInfo; --执行出错，回滚事务(指定事务名称)
  print @tran_error;
end 
else
begin
  commit tran tran_AddUserInfo; --没有异常，提交事务(指定事务名称)
  print @tran_error;
end

索引

1、创建索引

CREATE INDEX  index_name  on user_info(name)  ;普通索引
CREATE UNIQUE INDEX index_name on user_info(name)  ;唯一索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

2、查询索引

SHOW INDEX FROM table_name;

3、删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

授权 grant to

把查询Student表和修改学生学号number的权限授权给用户user.

grant select，update(number)
on table Student
to user;

把表Student的insert权限授予用户user，并允许将此权限再授予其他用户

grant insert
on table Student
to user
with grant option;

回收 revoke from

收回所有用户user对表student的查询权限

revoke select
on table student
from public;

视图

创建视图 create view

DROP VIEW IF EXISTS 视图名;

删除视图 drop view

DROP VIEW viewname;

数据查询语言DQL
数据定义语言DDL：创建数据库中的各种对象-----表、视图
数据操纵语言DML
数据控制语言DCL：授予回收数据库权限grant、rollback、commit

DDL数据库 create drop alter
DML数据表 insert delete update

alter add：增加表的字段

alter table 表名 add字段 类型 其他

alter table Student add number
alter table Student add number int(4) default '0'

alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

alter table employee add index emp_name (name);

alter table 表名 add primary key (字段名);

alter table employee add primary key(id);

alter table 表名 add unique 索引名 (字段名);

alter table employee add unique emp_name(number);

alter table 表名 drop index 索引名; alter drop

ALTER TABLE Student drop name;
alter table employee drop index name;

修改字段的数据类型

alter table Student modify name VARCHAR(12);

修改字段的数据类型并且改名

ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
alter table Student change name newname CHAR(18);

账号管理

进入数据库 mysql，查看所有用户账号列表

创建新账号

CREATE USER account_name IDENTIFIED BY ‘password’;
CREATE USER zhangsan IDENTIFIED BY ‘123456’;

使用 CREATE USER 创建用户账号，必须接着分配访问权限grant
账号重命名

RENAME USER old_name TO new_name;
RENAME USER zhangsan TO lisi;

重置账号密码

SET PASSWORD FOR account_name = Password(‘password’);
SET PASSWORD FOR zhangsan = Password(‘abcdef’);
SET PASSWORD = Password(‘10086’);

删除账号

USER zhangsan;

权限管理

查看账号的主机列表
SELECT user,host FROM user;

查看自己的账号（root）权限
SHOW GRANTS;

查看 zhangsan 的账号权限
SHOW GRANTS FOR zhangsan;

查看账号 root 在 localhost 下的权限
SHOW GRANTS FOR root@localhost;

为已存在的账号设置权限

GRANT <权限> ON <数据库名>.<表名> TO <账户名>;
GRANT SELECT, INSERT ON test.* TO ‘zhangsan’;

FLUSH PRIVILEGES;

授权后必须flush privileges，否则无法立即生效

创建账号并设置权限

USE test;
SHOW TABLES;
INSERT INTO user (username, email) VALUES ('zhangsan', 'zhangsan@gmail.com');
SELECT * FROM user;
UPDATE user SET email='lisiguang@gmail.com' WHERE username='lisi';
DELETE FROM user WHERE username='zhangsan';

撤销账号指定权限

正则表达式

regexp_like,
regexp_replace,
regexp_substr,
regexp_instr

元字符

regexp_like
查找ename中以a开头以n结尾的行. ^a n$
例如ename为arwen或arwin或anden.

regexp_replace

全文本搜索

CREATE TABLE语句接受FULLTEXT子句

Match()指定被搜索的列，Against()指定要使用的搜索表达式。

SELECT note_text from productnotes WHERE MATCH(note_text) AGAINST(‘rabbit’)

主键 外键

三种添加主键方式

触发器

包括INSERT语句、UPDATE语句、DELETE语句

查看表结构

FOUND_ROWS()

SELECT FOUND_ROWS();
最后一句SELECT查询到的总行数

游标 cursor