SQL语句汇总
一.开启mysql
mysql -u root -p
root
alter user 'root'@'localhost' identified by '新密码';//修改mysql密码
*命令不分大小写,但结束时必须用";",退出用/q;或exit;或quit;如果->是换行,结束时输入";"就可以了;
二.操作数据库
1>创建数据库
//create database if not exists mysql_test;
create database if not exists TS_050118200928_xuezhihui default character set utf8;
2>使用数据库
use mysql_test;
3>修改数据
alter database mysql_test
->default character set gb2312 //改变数据库默认字符集
->default collate gb2312_chinese_ci; //改变字符校对规则
4>查看数据库
//精确查找
show databases mysql_test;
//模糊查找
show databases like "mysql%";
5>删除数据库
drop database if exists mysql_test;
三.操作表
1.创建表
示例:
在mysql_test数据库下创建用户信息表infos,
表中属性包括:用户id,用户姓名,用户性别,用户住址,及用户联系方式
规定用户性别不能为NULL,默认是'M'且以用户id为用户信息表的主键;
请使用SQL命令创建该表;
use mysql_test;//切换至mysql_test数据库下;
mysql> create table borrowing
-> (
-> bid int not null,
-> bkid int not null,
-> booknumber int not null,
-> cardnumber int not null,
-> bdate date not null,
-> rdate date not null,
-> primary key(bid,bkid),
-> foreign key(booknumber) References book(booknumber),
-> foreign key(cardnumber) references user(cardnumber)
-> )ENGINE = InnoDB default charset = utf8;
insert mysql_test.infos values(2,'ZhangSan','M','beijing'),
->(2,'LiSi','female','shanghai')
->(3,'WangWu','M','wuhan');
desc mysql_test.infos; 查看表
select * from db_name.tbl_name; 查询表中所有列
create user 'zhangsan'@'NorthStar' identified by '123',
grant all on TS_050118200928_xuezhihui.* to'zhangsan'@'NorthStar';
create table if not exists infos
->(
->user_id int not null auto_increment, //设为自增的字段必须是主键,自增谨慎设置
->user_name char(50) not null,
->user_sex char(1) not null default 'M',
->user_address char(50) default null,
->user_contact char null,
->primary key(user_id)
->)ENGINE = InnoDB DEFAULT CHARSET=utf8;
create table if not exists user
-> (
-> cardnumber int not null,
-> name varchar(45) default null,
-> sex char(2) default null,
-> tel varchar(20) default null,
-> quantity int default null,
-> primary key(cardnumber)
-> )ENGINE=InnoDB default charset =utf8; //InnoDB 支持事务的数据库引擎
2.修改表
1>更改表的列名
alter table mysql_test.infos
->change column old_name new_name type limit default value;
2>往表中增加一列
alter table mysql_test.infos
->add column column_name type limit default value position;
//position为 first(第一列)/After column_name //MySQL增加列指定位置时没有BEFORE的用法
示例:
//向表infos中的用户姓名后加一列用户年龄属性
alter table mysql_test.infos
->add column user_age int null after user_name;
//向表infos中的加一列用户年龄属性,并置于第一列
alter table mysql_test.infos
->add column user_age int null First;
3>更改表中指定列类型定义---不干涉它的列名.
alter table mysql_test.infos
->modify column column_name type(width);
4>修改指定列默认值
alter table mysql_test.infos
->alter column user_sex set default 'FEMALE';
5>删除表中的指定列
alter table mysql_test.infos
->drop column column_name;
6>为表重命名
alter table db_name.tab_name
-> rename to db_name.new_tab_name;
rename to db_name.tab_name to db_name.new_tab_name; //不用alter table
3.查看表中的各个列
2>desc mysql_test.infos; //查看表 desc-->describe
1>show table like 'tbl_name'; //功能同上
3>show column from db_name.tab_name; //功能同上
4.删除表
drop table if exists mysql_test.infos; //可以连删 drop table if exists tab1,tab2
四.操作索引
0.索引分类:
*1.普通索引(Index或key)
*2.唯一索引(unique) :与普通索引类型不同的是唯一索引的列值必须唯一，但允许为空值。
*3.主键(primary key)
*索引通常被创建成单列索引和组合索引;
索引为DBMS根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表;
1.索引的创建
1>单列索引:
create index index_name
->on db_name.tbl_name(column_name(Length) asc/desc);
示例:
//在数据库mysql_test的表infos上根据客户姓名的前三个字符创建一个升序索引 index_infos;
create index index_infos
->on mysql_test.infos(user_name(3) asc);
2>组合索引
create index index_name
->on db_name.tbl_name(column1,column2)
示例:
//在数据库mysql_test的表infos上根据客户姓名和客户id号创建一个组合索引index_userom;
2.使用create table 语句来创建索引
1>在创建新表时创建表的主键
primary key(col_name,...), //可以是单个主键也可以是联合主键
2>在创建表时创建该表的索引
{index/key} index_name(col_name),
3>在创建表时创建该表的唯一性索引
unique index_name(col_name),
4>在创建表时创建该表的外键
foreign key index_name(col_name),
示例:
在已有数据库mysql_test上新建一个包含seller_id,seller_name,seller_address
,seller_contact,product_type,当月销量(sales)等内容的产品卖家信息表seller
同时为该表添加由卖家id号和售卖产品类型组成的联合主键,并在当月销量上创建索引;
create table if not exists seller
-> (
-> seller_id int not null auto_increment,
-> seller_name char(20) null,
-> seller_address char(50) null,
-> seller_contact char(20) null,
-> product_type char(30) not null,
-> sales int not null,
-> primary key(seller_id,product_type), //创建表的主键
-> index index_sales(sales) //创建一般索引
-> );
3.使用alter table语句创建索引
1>在修改表的同时为表添加索引:
add{index/key} index_name(col_name);
示例:为表seller中的seller_name创建索引 index_seller_name;
alter table mysql_test.seller
-> add index index_seller_name(seller_name);
2>在修改表的同时为该表添加主键
add primary key(col_name,...)
3>在修改表的同时为该表添加唯一性索引
add unique key(col_name,...)
4>在创建新表的同时为该表添加外键
add[constraint[symbol]] foreign key(col_name) references tab2(col_name)
示例:
alter table mysql_test.SC
add foreign key(CNO) references CT(CNO);
references tbl_name(指定外键所参照的表名)
(index_col_name,...)(指定外键名)
[on delete reference_option(restrict|cascade|set null|no action)]
[on update restrict|cascade|set null|no action](指定实现完整性约束的策略)
参照完整性约束:
reference_option:(限制语句包括以下几种)
*1.restrict :限制策略(限制删除和更新主键或外键)
*2.cascade :级联策略(将被参照表的外键删除)
*3.set null :置空策略(将参照表的主键设为空,被参照表才能删除外键)
*4.no action:不采取实施策略(不允许操作)
示例:
mysql> use mysql_test (指定数据库mysql_test)
Database changed
mysql> create table orders (创建表orders)
-> ( (以下指定orders表中的各属性列)
-> order_id int not null auto_increment,
-> order_product char(50) not null,
-> order_product_type char(50) not null,
-> user_id int not null,
-> order_date datetime not null,
-> order_price double not null,
-> order_amount int not null,
-> primary key(order_id), (给orders表设置主键)
-> foreign key(user_id) (给orders表设置外键)
-> references infos(user_id)(开始参照完整性约束,指定参照表)
-> on delete restrict (不允许删除外键)
-> on update restrict (不允许更换外键)
-> );
*unique key index_name 候选键
4.索引的查看
show {index|keys} {from/in} tbl_name;
示例:
show keys from mysql_test.seller;
5.索引的删除
1>删除普通索引
drop index index_name on tbl_name;
示例:
drop index index_seller_name on mysql_test.seller;
2>使用alter table 语句删除索引
alter table tbl_name drop index index_name;//删除普通索引
alter table tbl_name drop primary key;//删除主键
alter table tbl_name drop foreign key;//删除外键
示例:
alter table mysql_test.seller drop primary key;
*设为自增的主键删除不了要删除就先去掉自增
五.表中数据更新(增,删,改)
1.插入数据(insert)
1>插入完整列值数据(insert...value)
insert tbl_name(col_name,...){values}(col_value,....),(..);
示例:
insert mysql_test.infos values(2,'ZhangSan','M','beijing'),
->(2,'LiSi','female','shanghai')
->(3,'WangWu','M','wuhan');
2>插入部分列值数据(insert...set)
insert table set col_name=value,...;
示例:
insert mysql_test.infos
->set user_name='ZhaoLiu',user_city='guangzhou';
3>插入子查询数据(insert...select)
//选择值赋给对应字段
insert into tbl_name2(col_name1,col_name2,...) select value1,value2,...;
示例:
insert mysql_test.infos(user_name,user_sex,user_city)select 'liYunLong','M','HeBei';
//将表1中部分数据复制插入到表2中
insert into tbl_name2(a,c,b...) select a,c,5... from tbl_name1;
2.删除一行或多行数据(delete from...where..)
delete from tbl_name where where_condition
order by...limit row_count;
示例:
delete from mysql_test.infos where user_name='xiuX';
//删除多行--方法1.
delete from mysql_test.infos where user_name in('xiuX','LI');
//删除多行--方法2.
delete from mysql_test.infos where user_name='xiuX'or user_name='LI';
3.修改数据(update...set...where...)
update tbl_name set col_name=new_value where col_name=value;
示例:
update mysql_test.infos set user_name='LiSi' where user_city='guangzhou';
六.表中数据查询(select)
╭#1.select语句---------------要返回列或表达式(必须使用)
│#2.列的选择与指定
│#3.from子句与多表连接查询-----从中检索数据的表(仅在从表中选择数据时使用)
│#4.where子句与条件查询-------行级过滤(不是必须使用)
数据查询<
│#5.group by子句与分组数据----分组说明(仅在按组计算聚合时使用)
│#6.having子句---------------组级过滤(不是必须使用)
│#7.order by子句-------------输出排序顺序(不是必须使用)
╰#8.limit子句----------------要检索的行数(不是必须使用)
1.select语句(综合应用)
[ALL|distinct|distinctRow]
select select_expr(要查的值)
from table_references(数据来源),
where where_condition(数据的选择条件)
group by col_name|expr|position(对检索到的记录进行分组)
ASC|DESC, with rollup
having where_condition(指定组的选择条件)
order by col_name|expr|position(对查询结果进行排序)
asc|desc,...
limit offset row_count|row_count Offset offset(限制行数)
1>列的选择和指定
1)查询某表指定列
select col_name1,col_name2,..from db_name.tbl_name;
示例:查询数据库mysql_test的表infos中各个客户的姓名信息
select user_name from mysql_test.infos;
2)查询表中所有列
select * from db_name.tbl_name;
示例:查询数据库mysql_test的表infos中各个客户的所有信息
select * from mysql_test.infos;
2>定义并使用列的别名,来查询该列
//这个查询的只是虚表,表中真实的col_name没有变;
select col_name as col_alias(列的别名) from db_name.tbl_name;
示例:
select user_city as user_address from mysql_test.infos;
3>替换查询结果集中的列的数据的值(case)
select col_name1,col_name2,...,
->case
->when 条件1 then 表达式1
->when 条件2 then 表达式2
->else 表达式
->end as col_alias
->from db_name.tbl_name;
示例:
查询数据库mysql_test的表infos中用户的的user_name列和user_sex列，
要求判断结果集中user_sex列的值，如果该列的值为M，则显示输出“男”，
否则为“女”，同时在结果集的显示中将user_sex列用别名“性别”标注。
mysql> select user_name,user_city,
-> case
-> when user_sex='M' then '男'
-> when user_sex='Female' then '女'
-> else '无性别'
-> end as '性别'
-> from mysql_test.infos;
4>计算列值
//表中的user_name,以及对user_id列加上数字100后的值
select user_name,user_sex,user_id+3 from tbl_name;
+-----------+-------------+
| user_name | user_id+100 |
+-----------+-------------+
| zhangsan | 101 |
| lisi | 104 |
| wangwu | 108 |
+-----------+-------------+
2.聚合函数
*含义:是系统内置函数,经常与group by联合使用;
1)count 求组中项数,返回int类型整数
2)max 求最大值
3)min 求最小值
4)sum 返回表达式中所有值的和
5)avg 求组中值的平均值
6)std或stdDev 返回给定表达式中所有值的标准值
7)variance 返回给定表达式中所有值的方差
8)group_concat 返回由属于一组的列值连接组合而成的结果
9)bit_and 逻辑与
10)bir_or 逻辑或
11)bit_xor 逻辑异或
3.from子句与多表连接查询(综合应用)
1>交叉连接
概念:又称笛卡尔积(属性数相加(s+r),元组数相乘(m*n))
select * from tab1 cross join tab2;
select * from tab1, tab2;//功能同上
示例:
mysql> select * from infos,seller;
2>内连接
*1.概念:
内连接是一种最常用的连接类型,它是通过在查询中设置连接条件的方式,
来移除查询结果集中某些数据行之后在进行交叉连接
*2.分类:
#1.等值连接:使用运算符"=" //这个是考点
#2.非等值连接:使用除"="之外的其他比较运算符(>,<,!=);
#3.自连接:将一个表与它自身连接;
*3.语法:
select * from table1 inner join(内连接) table2
on tab1.col_name 比较运算符 tab2.col_name //连接条件
示例:
select * from infos inner join seller on infos.user_name=seller.seller_name;
+---------+-----------+----------+-----------+-----------+-------------+----------------+
| user_id | user_name | user_sex | user_city | seller_id | seller_name | seller_address |
+---------+-----------+----------+-----------+-----------+-------------+----------------+
| 4 | lisi | Female | guangzhou | 6 | lisi | NULL |
+---------+-----------+----------+-----------+-----------+-------------+----------------+
3>外链接
*1.左外链接
#1.概念:
也称左连接,以左表为基表，在from子句中使用关键字 "left outer join"
或关键字 "left join" 来连接两张表。返回指定左表的全部行+右表对应的行,
如果左表中的数据在 右表中 没有与之相匹配的行,则在查询结果集中显示为 空值(NULL);
#2.语法
select * from tab1 left join tab2 //tab1为基表
->on on tab1.col_name = tab2.col_name;
#3.示例:(以左表为基表--基表显示全部元组)
mysql> select * from infos left join seller on infos.user_name=seller.seller_name;
+---------+-----------+----------+-----------+-----------+-------------+-------+
| user_id | user_name | user_sex | user_city | seller_id | seller_name | sales |
+---------+-----------+----------+-----------+-----------+-------------+-------+
| 4 | lisi | Female | guangzhou | 6 | lisi | 20 |
| 1 | zhangsan | M | beijing | NULL | NULL | NULL |
| 8 | wangwu | NULL | shanghai | NULL | NULL | NULL |
+---------+-----------+----------+-----------+-----------+-------------+-------+
*1.右外链接
#1.概念:
也称右连接,以右表为基表，在FROM子句中使用关键字 “right outer join”
或关键字 “right join” 来连接两张表,返回指定右表的全部行+左表对应的行,
如果右表中的数据在 左表中 没有 与之相匹配的行,则在查询结果集中显示为 空值(NULL);
#2.语法
select * from tab1 right join tab2 //tab1为基表
->on on tab1.col_name = tab2.col_name;
#3.示例:(以右表为基表--基表显示全部元组)
mysql> select * from infos left join seller on infos.user_name=seller.seller_name;
+---------+-----------+----------+-----------+-----------+-------------+-------+
| user_id | user_name | user_sex | user_city | seller_id | seller_name | sales |
+---------+-----------+----------+-----------+-----------+-------------+-------+
| NULL | NULL | NULL | NULL | 1 | X | 10 |
| NULL | NULL | NULL | NULL | 2 | X2 | 12 |
| NULL | NULL | NULL | NULL | 3 | X3 | 13 |
| 4 | lisi | Female | guangzhou | 6 | lisi | 20 |
+---------+-----------+----------+-----------+-----------+-------------+-------+
4.where子句与条件查询
1>比较运算: 比较两方有null值除<=>这个符号.,其他返回unknown.<=>返回true/false;
2>条件查询:
select * from tbl_name where where_condition;
示例:
select * from infos where user_sex='M';
3>查询范围
*1.当查询的过滤条件被限定在值的某个范围时,可以使用语句"between..and":
select {col_name|*} from tab where col_name [NOT] BETWEEN expr1 AND expr2
当不使用关键字“not”时，值在表达式expr1和expr2之间（包括这两个值）
如果使用关键字“not”时，其返回值正好相反。<expr1或>expr2
示例:
select * from seller where sales between 12 and 13; //包括12,13
select * from seller where sales not between 12 and 13; //不包括 12,13
*2.使用关键字"in" 可以指定一个值的枚举表,该表中会根据in后的范围列出所有可能的值.
select * from tab where col_name in(,..n);
select * from seller where sales in(12,15,20);
4>查找指定列的值为空的元组(is null)
select {col_name|*} from tab where col_name is NULL;
示例:
//查找seller_contact列值为空的元组
mysql> select seller_name from seller where seller_contact is null;
//查找seller_contact列值为不为空的元组
mysql> select seller_name from seller where seller_contact is not null;
5>子查询
*1.根据返回结果分类:(***解答题***)
#1.表子查询:返回的结果集是一个表。
#2.行子查询:返回的结果集是带有一个或多个值的一行数据。
#3.列子查询:返回的结果集是一列数据，该列可以有一行或多行，但每行只有一个值。
#4.标量子查询:返回的结果集仅仅是一个值。
*2.子查询结合关键字"in" 使用的子查询,主要用于判断一个给定的值是否存在于子查询的结果集中.
语法: expression(表达式) in subQuery(子查询集)
mysql> select user_name
-> from SNO where SNO in (找到外键,用外键连接两个表)
-> (select SNO from tbl_score where score>80); (循环嵌套选择列)
取反(<=80)
mysql> select SNO,student_name
-> from student
-> where SNO not in(select SNO from tbl_score where score>80);
*3.结合比较运算符使用子查询
语法:expression{=,<=,>,>=,<=>,<>,!=}{all|some|any}(subQuery)(子查询语句)
*:in-> =any-> =some;
mysql> select SNO,student_name
-> from student
-> where SNO =any(select SNO from tbl_score where score>80);
mysql> select SNO,student_name
-> from student
-> where SNO =some(select SNO from tbl_score where score>80);
*:not in-><>all
mysql> select SNO,student_name
-> from student
-> where SNO <>all(select SNO from tbl_score where score>90);
*:=(精确到条目)
mysql> select SNO,student_name
-> from student
-> where SNO =(select SNO from tbl_score where score=90);
*4.结合关键字"exists"使用的子查询
语法:exists(subQuery)
mysql> select SNO,student_name
-> from student
-> where exists(select SNO from tbl_score where student.SNO=tbl_score.SNO);
*5.指定查询条件的WHERE子句
在指定查询条件的WHERE子句中，可以使用子查询的结果集作为过滤条件的一部分，
并且子查询通常会与关键字“IN”“EXIST”和比较运算符结合使用。
#1.结合关键字“IN”使用的子查询：主要用于判定一个给定值是否存在于子查询的结果集中。
#2.结合比较运算符使用的子查询：主要用于将表达式的值和子查询的结果进行比较运算。
#3.结合关键字“EXISTS”使用的子查询：主要用于判定子查询的结果集是否为空。
5.group by子句与分组数据(综合应用)
1)语法:
/**
* col_name1 指定聚合函数的条件依据
* col_name2 指定用于分组的选择列
* expr 指定用于分组的表达式
* position 指定用于分组的列在SELECT语句结果集中的位置，通常是一个正整数
* asc|desc ASC表示升序排列， DESC表示降序。默认ASC
* with rollup 可选项，指定在结果集中不仅包含由GROUP BY子句分组后的数据行，
* 还包含各分组的汇总行，以及所有分组的整体汇总行，可以得到每个分组以及每个分组汇总级别的值
* 作用:去重复,查找表CT中有多少讲师记录(按姓名分组,排重)
*/
select col_name1,...,from tbl_name
group by{col_name2|expr|position}[asc|desc],...[with rollup]
示例数据库
+-----+----------+-------+
| CNO | CTitle | CName |
+-----+----------+-------+
| 1 | 数据库 | 小涵 |
| 2 | 数据结构 | 爱敏 |
| 3 | 信息系统 | 王月 |
| 4 | C++ | 爱敏 |
+-----+----------+-------+
示例:将表TC按课程和讲师姓名分组,根据讲师姓名获取每位讲师的授课项数;
mysql> select CName,count(*) as '授课项数'
-> from CT group by CName,CTitle;
+-------+--------+
| CName | 授课项数|
+-------+--------+
| 小涵 | 1 |
| 爱敏 | 2 |
| 王月 | 1 |
+-------+--------+
示例:将表TC按课程和讲师姓名分组,根据讲师姓名获取每位讲师的授课项数及授课汇总;
mysql> select CTitle,CName,count(*) as '授课项数'
-> from CT group by CName,CTitle with rollup;//(汇总)(有总字就加这个)
+----------+-------+----------+
| CTitle | CName | 授课项数 |
+----------+-------+----------+
| C++ | 爱敏 | 1 |
| 数据结构 | 爱敏 | 1 |
| NULL | 爱敏 | 2 |--->爱敏授课数总计2项
| 信息系统 | 王月 | 1 |
| NULL | 王月 | 1 |--->王月授课数总计1项
| 数据库 | 小涵 | 1 |
| NULL | 小涵 | 1 |--->小涵授课数总计1项
| NULL | NULL | 4 |--->全部授课数共计4项
+----------+-------+----------+
*简述对于 GROUP BY 子句的使用，需要注意哪些内容？
1.GROUP BY 子句可以包含任意数目的列，使得其可对分组进行嵌套，为数据分组提供更加细致的控制。
2.如果在 GROUP BY 子句中嵌套了分组，那么将按 GROUP BY 子句中列的排列顺序的逆序方式依次进行汇总，
并将在最后规定的分组上进行一个完全汇总。
3.GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。
4.除聚合函数之外， SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
5.如果用于分组的列中含有 NULL 值，则 NULL 将作为一个单独的分组返回；
如果该列中存在多个 NULL 值，则将这些 NULL 值所在的行分为一组。
6.Having子句(综合应用)
1>having子句
示例:
在数据库mysql_test的表CT中查找课程信息,要求在返回的结果集中
列出授课老师授课数数大于1的所有老师姓名;
/**
* select 要被过滤条件项 from 表 group by 分组项 having 过滤项的过滤条件
*/
mysql> select CName from CT group by CName having count(*)>1; //对分组项的过滤
+-------+
| CName |
+-------+
| 爱敏 | --->having过滤分组项;可以包含聚合函数,在数据分组后进行过滤;
+-------+
2>where子句
mysql> select * from CT where CTitle='数据库';
+-----+--------+-------+
| CNO | CTitle | CName |
+-----+--------+-------+
| 1 | 数据库 | 小涵 |--->Where过滤数据行,不可以包括聚合函数,在数据分组前进行过滤;
+-----+--------+-------+
3>HAVING 子句与 WHERE 子句两者之间存在哪些差异？：
*1.WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，
即 HAVING 子句可基于分组的聚合值而不是特定行的值来过滤数据。
*2.HAVING 子句中的条件可以包含聚合函数，而 WHERE 子句中则不可以。
*3.由于WHERE 子句会在数据分组前进行过滤， HAVING 子句则会在数据分组后进行过滤。因而，
WHERE 子句排除的行不包含在分组中，这就会可能改变计算值，从而影响 HAVING 子句基于这些值过滤掉的分组。
7.order by 子句(指定排序的列)
order by col_name|expr|position ASC|DESC
示例:
select CTitle,CName from CT order by CName desc;
8.limit子句
/*
* 使用limit子句限制select语句返回的行数
* limit row_count: 取row_count行,从第一行开始(起始行的位置0代表第1行)
* row_count offset:取row_count行,从offset+1行起,
*/
limit {row_count | row_count offset}
示例:从表CT的第1行起取2行数据;
mysql> select CNO,CTitle from CT order by CNO
-> limit 2 offset 0;(或写成 limit 2,0)
+-----+----------+
| CNO | CTitle |
+-----+----------+
| 1 | 数据库 |
| 2 | 数据结构 |
+-----+----------+
七.操作视图
1.定义:视图是数据库中的一个对象,它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制;
2.创建视图(综合应用)
create or replace view view_name[(col_list)]
as select statement(条件)
[with check option] 可选项,指定在可更新视图上必须符合..条件时使用 (不常用)
mysql> create or replace view mysql_test.infos_view
-> as select * from mysql_test.infos
-> where user_sex='M'
-> with check option;
3删除视图(综合应用)
drop view[if exists]
view_name [,view_name]...
[restrict|cascade] 限制条件 可选项
mysql> drop view if exists
-> mysql_test.infos_view;
4.修改视图定义(综合应用)
alter view view_name[(col_list)]
as select statement(条件)
[with check option] 可选项,指定在可更新视图上必须符合..条件时使用 (不常用)
5.查看视图定义(综合应用)
show create view view_name
6.更新视图数据(综合应用)
1)插入行完整列值数据(insert..values)
insert view_name(col_name,...){values}(expr|Default,...),(..),
mysql> insert mysql_test.infos_view
-> values(901,'zhansan','F','beijing','chaoyang');
2)更新表数据(insert..set)
update view_name
set col_name=expr|Default,col_name2=expr|Default,....
where where_condition
order by... limit row_count
mysql> update mysql_test.infos_view
-> set user_address='wuhanshi'
-> where user_name='zhansan';
3)删除视图表数据(delete..from)
delete from view_name
where where_condition
order by... limit row_count
mysql> delete from mysql_test.infos_view
-> where user_name='lisi';
7.查询视图数据(综合应用)
mysql>select user_name,user_address
-> from mysql_test.infos_view
-> where user_id =905;
八.数据库编程
1.存储过程
1>概念
存储过程是一组为了完成某项特定功能的SQL语句集,其实质就是一段存储在数据库中的代码.
它可以由声明式的sql语句和过程式的sql语句组成.
*1.声明式的sql语句:create,update,select;
*2.过程式的sql语句:case if else
2>创建存储过程(解答题)
1)delimiter命令(分隔符)
delimiter$$-->用户自己定义结束符(替换";"告诉系统语集中的";",并不是结束)
mysql> delimiter $$ (使用$$将;替换成$$)
mysql> show databases;
-> $$
2)使用create procedure语句创建存储过程;
create procedure sp_name (在默认数据库中创建)
([proc_parameter[,...]]) (指定存储过程的参数结果)
routine_body (存储过程的主体部分,也称存储过程体)
[in|out|inout]param_name type(设置参数)
mysql> use mysql_test;(指定默认数据库)
Database changed
mysql> delimiter $$(设定结束符)
mysql> create procedure sp_update_sex(in uid int,in sex char(20))
-> begin (语句集开始)
-> update infos set user_sex=sex where user_id=uid;(根据id号修改性别)
-> end $$ (语句集结束)
3>存储过程体
1)使用declare语句声明局部变量
declare var_name[,...] type [default value] (名字,类型及默认值)
->begin
declare cid int(10);(存储过程的开头处声明,作用范围仅限于begin..end内)
->end
2)使用set语句为局部变量赋值
set var_name=expr[,var_name=expr]
set cid=910;
3)使用select..into语句把选定列的值直接存储到局部变量中
select col_name[,...](指定列名)
into var_name[,...](指定要赋值的变量名)
table_expr (表示select语句中的from子句及后面的语法部分)
*.存储过程体中的SELECT…INTO语句返回的结果集只能有一行数据。
4)流程控制语句
*1.条件判断语句
if...then...else语句--> if 条件 then 表达式1 else 表达式2
case语句--> case 条件1 when 条件 then
*2.循环语句
while语句--> while 条件 表达式(累加/减) end while
repeat语句--> repeat 表达式 end repeat
loop语句--> loop 表达式 end loop
iterate语句--> 用于表示退出当前循环,进行下一次循环(同continue)
5)使用declare cursor语句---声明游标
declare cursor_name (指定要创建的游标名称)
cursor for select_statement
6)使用open语句--------------打开游标
open cursor_name (指定要打开的游标)
7)使用fetch...into语句------读取数据
fetch cursor_name (读取被指定的已打开的游标)
into var_name[,var_name]...(指定存放数据的变量名)
8)使用close-----------------关闭游标
close cursor_name
简述在 MySQL 中使用游标的过程(解答题)
#1.声明游标：用 DECLARE CURSOR 语句创建游标;
#2.打开游标：用 OPEN 语句打开游标;
#3.读取数据：用 FETCH...INTO 语句从中读取数据。
FETCH 语句是将游标指向的一行数据赋给一些变量，
这些变量的数目必须等于声明游标时 SELECT 子句中选择列的数目。
游标相当于一个指针，它指向当前的一行数据;
#4.关闭游标：用 CLOSE 语句关闭游标;
4>调用存储过程(解答题)
使用call语句调用存储过程
call sp_name([palameter[,...]]) (指定被调用的存储过程的名称及当下要用的参数)
call sp_name[()]
示例:调用数据库mysql_test中的存储过程sp_update_sex,
将客户id号为8的客户性别修改为男性'M';
call sp_update_sex(8,'M');
5>删除存储过程(选择填空)
使用drop procedure语句删除存储过程
drop procedure [if exists] sp_name;
2.存储函数
1>存储函数的基本概念(填空选择)
存储函数与存储过程一样,是由SQL语句和过程式语句的代码片段.
简述存储函数和存储过程的区别
*1.存储函数不能输出参数,这是因为存储函数自身就是输出参数;存储过程可以拥有输出参数
*2.存储函数可以直接调用,不需要call语句,存储过程的调用需要call语句进行
*3.存储函数必须包含一条return语句,存储过程不允许有return语句
2>创建存储函数(解答题)
create function sp_name([param_name type]) (指定存储函数的名称及形参)
returns type (声明存储函数返回值的数据类型,type指定返回值的数据类型)
[deterministic] (只要输入时间是确定的，返回结果也是确定的，因此是一个确定性函数。默认不确定)
routine_body (指定函数体)//begin..end
示例:在数据库mysql_test中创建一个存储函数,要求该函数能根据给定的用户id号
返回客户的性别,如果数据库中没有给定的用户id号则返回"没有该用户";
mysql> use mysql_test
Database changed
mysql> delimiter $$
mysql> create function fn_search(uid int)
-> returns char(20)
-> deterministic
-> begin
-> declare sex char(20);
-> select user_sex into sex from infos
-> where user_id=uid;
-> if sex is NULL then
-> return (select '没有该用户');
-> else if sex='F'then
-> return (select '女');
-> else return(select '男');
-> end if;
-> end if;
-> end $$
//相对应的程序伪代码
char fn_search(int uid){
char sex;
for(int i=0;i<infos.length;i++){
if(uid==infos[i].user_id){
sex=infos.user_sex;
if(sex='null') return '没有该用户'
else if (sex='F') return '女'
else return '男'
}
}
}
3>调用存储函数(填空选择)
select sp_name([func_parameter[,...]])
select fn_search(903)
4>删除存储函数(填空选择)
drop function[if exists] sp_name;
九.触发器的操作
1>创建触发器
1)概念:是用户定义在关系表上的一类由事件驱动的数据库对象,
也是一种保证数据完整性的方法;(例:更新被参照表,参照表就会自动更新)
2)使用create trigger语句创建触发器
create trigger
trigger_name (指定触发器名字)
trigger_time (指定触发事件)
trigger_event (指定触发事件)
on table_name (指定触发对象(触发器相关的表名))
for each row (对于受触发事件影响的每一行都要激活触发器的动作)
trigger_body; (指定触发器动作的主体)
示例:在数据库mysql_test的表infos中创建一个触发器 infos_insert_trigger,
用于每次表infos插入一行数据时,将用户变量str的值设置为 one info added!
mysql> create trigger infos_insert_trigger //指定触发器的名称
-> after insert //指定触发事件
-> on mysql_test.infos for each row //指定受触发事件影响的表及每一行都有激活触发器的动作;
-> set @str='one info added!'; //指定触发器动作的主体;
//检验触发器的触发事件是否成功:
mysql> insert infos set user_name='wangmei',user_sex='F',user_city='hunan';
Query OK, 1 row affected (0.22 sec)
mysql> select @str; //设置成功
+-----------------+
| @str |
+-----------------+
| one info added! |
+-----------------+
1 row in set (0.00 sec)
2>使用触发器(解答实操)
1)insert 触发器
在insert触发器代码内,可引用一个名为new的虚表,来访问被插入的行
在before insert触发器中,new中的值可以被更新;
示例:在mysql_test的表infos中重新创建触发器infos_insert_trigger,
用于每次向表infos插入一行数据时,将用户变量str的值设为新插入客户的id号;
mysql> create trigger mysql_test.infos_insert_trigger
-> after insert on mysql_test.infos for each row
-> set @str=new.user_id;
2)delete 触发器
在delete触发器代码内,可引用一个名为old的虚表,来访问被删除的行
mysql> create trigger mysql_test.infos_insert_trigger
-> after delete on mysql_test.infos for each row
-> set @str=old.user_id;
3)update 触发器
在update触发器代码内,可以引用old的虚表来访问执行前的值,也可以用new虚表来访问更新后的值;
注:UPDATE触发器可在UPDATE语句执行之前或之后执行。
使用该触发器时，需要注意：当触发器涉及对触发表自身的更新操作时，
只能使用BEFORE UPDATE触发器，而AFTER UPDATE触发器将不被允许。
mysql> create trigger mysql_test.infos_update_trigger
-> before update on mysql_test.infos for each row
-> set new.user_address=old.user_contact;
3>使用drop语句删除触发器
drop trigger[if exists]
database_name.trigger_name);(指定触发器所在数据库和触发器的名称
mysql> drop trigger if exists mysql_test.infos_insert_trigger;
十.数据库安全性与访问控制
1>用户账号管理(选择填空)
1)root用户:
mysql> select user from mysql.user;
2)使用create user语句创建mysql账户
create user user(指定创建用户账号,格式:'user_name'@'host name'(计算机名))
[identified by [password]'password'] (可选项,指定用户账号对应口令通过密码识别)
3)使用select password(num) 获取散列值
mysql> select md5(456);;
4)使用drop user 'user_name'@'host name'删除用户
mysql> drop user zhangsan@NorthStar;
示例:在Mysql服务器中添加两个新用户,其用户名分别为zhangsan和lisi,
他们的主机名为localhost(这个操作时必须是真实主机名),
用户zhangsan的口令为123,用户lisi的口令为对明文456
使用md5()函数加密返回的散列值;
mysql> select password(456);(获取456的散列值)
mysql> create user 'zhangsan'@'NorthStar' identified by '123',
-> 'lisi'@'NorthStar'identified by password
-> '*531E182E2F72080AB0740FE2F2D689DBE0146E04';
5)使用rename user语句修改一个或多个已经存在的用户账号。
rename user old_user (已存在的名字)
to new_user (修改后的名字)
[,old_user to new_user]...
mysql> rename user 'zhangsan'@'NorthStar' to 'wangwu'@'NorthStar';
6)使用set password 语句修改用户登录口令
update set password for 'user_name'@'localhost'
=password('new_password')|'encrypted password'
mysql> set password for 'wangwu'@'NorthStar'=password('123');
*1.MySQL的用户账号及相关信息都存储在一个名为mysql的MySQL数据库中,
这个数据库里有一个名为user的数据表,包含了所有用户账号.
并且它用一个名为user的列存储用户的登录名。
*2.mysql安装时，设置用户名为“root”，作为一个新安装的系统，当前只有一个名为root的用户。
这个用户是在成功安装MySQL服务器后，由系统创建的，并且被赋予了操作和管理MySQL的所有权限。
因此，root用户拥有对整个MySQL服务器完全控制的能力。
2>账号权限管理(选择填空)
1)使用grant语句为用户授权(grant...to)
grant priv_type(用于指定权限名称)
[column_list](用于指定授权表中具体的列)
[,priv_type[col_list]]
on[object_type] priv_level(指定类别及级别)
to user_specification[,user-specification](用于设定用户口令与密码[1])
[with grant option] (实现权限的转移和限制)(可以授权给别人)
[1]user[identified by [password]'password']
mysql> grant select(user_id,user_name) --列级授权
-> on mysql_test.infos
-> to 'wangwu'@'NorthStar';
2)在grant语句中使用with子句实现权限转移(grant...to(授权用户)...with(授权转移)
例:授予当前系统中一个不存在的用户zhou在数据库mysql_test表infos上
拥有select和update的权限,并允许其可以将自身的这个权限授予其他用户;
mysql> grant select,update --表级权限
-> on mysql_test.infos
-> to'zhou''NorthStar'identified by '123'
-> with grant option;
mysql> grant all --数据库级别权限
-> on mysql_test.*
-> to'wangwu'@'NorthStar';
例:授予系统中已存在的wangwu拥有创建用户的权限
3)在此语法格式中:语法项“priv_level”:用于指定权限的级别，其可以授予的权限有这样几个：
列权限、表权限、数据库权限和用户权限。
*1.授予表权限时，可授予的权限有：
SELECT、INSERT、DELETE、UPDATE、REFERENCES、CREATE、ALTER、INDEX、DROP、ALL。
*2.授予列权限时,只可授予SELECT、INSERT和UPDATE这三种权限,同时权限的后面要加上列名column_list。
*3.授予数据库权限时，可授予的权限除了可以指定为授予表权限时的所有值之外，还可以是下面这些值:
REATE TEMPORARY TABLES、CREATE VIEW、SHOW VIEW、CREATE ROUTINE、
ALTER ROUTINE、EXECUTE ROUTINE、LOOK TABLES。
*4.最有效率的权限是用户权限,授予用户权限时,除了可以指定为授予数据库权限时的所有值之外
还可以是下面这些值：CREATE USER、SHOW DATABASES。
mysql> grant create user
-> on *.*
-> to 'wangwu'@'NorthStar';
4)使用revoke语句撤销用户权限
revoke priv_type(用于指定要收回权限名称)
[column_list](用于指定要收回权限列)
[,priv_type[col_list]]
on[object_type] priv_level(指定要收回权限类别及级别)
from user_specification[,user-specification](用于设定被收回权限的用户)
mysql> revoke select
-> on mysql_test.infos
-> from 'wangwu'@'NorthStar';
十一.数据库的备份与恢复
1)数据备份是指通过导出数据或者复制表文件的方式来制作数据库的复本;
2)数据库恢复则是当数据库主线故障或遭到破坏时,将备份的数据库加载到系统,
从而使数据库从错误状态恢复到备份时的正确状态.
数据库的恢复是以备份为基础的,它是与备份相对应的系统维护和管理操作;
3)使用select into...outFile语句备份数据(拷出)
select * into outFile 'file_name' (文件名称)
export_options (配置文件表达式[1])
| into dumpfile 'file_name'
[1]
[field (决定数据行在备份文件中存储的格式)
[terminated by 'string'] (指定字段之间的符号)
[optionally enclosed by 'char'] (指定包裹文件中字符值的符号)
[escaped by 'char']] (指定转义字符)
[lines terminated by 'string'] (指定数据行结束的标志)
*.表备份
mysql> select * from mysql_test.infos
-> into outfile 'E/:BACKUP/backupfile.txt'
-> fields terminated by ','
-> optionally enclosed by ""
-> lines terminated by'?';
4)使用load data...infile语句恢复数据
*.将备份导入空表中
mysql> load data infile 'E/:BACKUP/backupfile.txt'
-> into table mysql_test.infos_copy
-> fields terminated by ','
-> optionally enclosed by ""
-> lines terminated by'?';
5)数据的并发处理
以begin transaction语句开始;
以commit(提交)语句或rollback(回滚)语句结束;
十二.数据库系统原理PHP部分
1.通过PHP编程语言编写相应的服务端代码,完成前台页面与后台数据的交互操作,具体建立与MYSQL数据库服务器的链接.
2.PHP内置了许多函数,通过使用内置函数 mysql,PHP程序可以很好地与MYSQL数据库进行交互.
3.PHP操作MYSQL数据库的基本步骤如下:
1>首先建立与MYSQL数据库服务器的连接;
2>然后要对其进行操作的数据库;
3>再执行相应的数据库操作,包括对数据的添加,删除,修改和查询等
4>最后关闭与MYSQL数据库服务器的连接
4.建立数据库连接的方法(mysql()函数的用法)
//localhost:3306表示连接本地主机中的MYSQL数据库服务器
mysql_php程序名("localhost:3306","用户名","密码");
示例:
编写一个数据库服务器的连接示例程序connect.php,要求以超级用户root及其密码123456
连接本地主机的MYSQL数据库服务器,并使用$con保存连接的结果,其过程如下:
connect.php //文件名,用文本编辑器打开编辑
<?php //php程序时被包含在标记符<?php ?>之间的代码
/**
* php 连接数据库三要素：数据库服务器ip地址、数据库用户名、数据库密码。
* mysql_connect();连接数据库,返回布尔值,
* $conn:true:连接成功,false连接失败
*/
$conn=mysql_connect("localhost:3306","root","123456");//定义变量以$开头
if(!$conn){
echo"连接失败!<br>"; //echo语句返回信息
echo"错误编号: "mysql_errno()"!<br>"; //<br>为换行标记符
echo"错误信息:"mysql_err()"<br>"; //echo语句返回信息
die(); //终止程序运行
}
echo"连接成功!<br>";
?>
1493
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
