数据库系统原理 SQL 杂记

作者: gaookey | 来源:发表于2020-10-19 23:05 被阅读0次

进入数据库

mysql -u root -p

创建数据库

create database if not exists mysql_test;

显示数据库

show databases;

删除数据库

drop database mysql_test;

使用数据库

use mysql_test;

创建表格

mysql> create table customers 
    -> (
    -> c_id int not null auto_increment,
    -> c_name char(50) not null,
    -> c_sex char(1) not null default 0,
    -> c_address char(50) null,
    -> c_contact char(50) null,
    -> primary key(c_id)
    -> );

// 添加 temporary 为临时表
create temporary table customers

整型 int
浮点型 double
布尔型 bool
日期型 date
时间戳 timestamp
时间型 time
定长字符类型 char
可变长字符 varchar

添加一列

向数据库 mysql_test 添加一列，并命名为 c_city，要求其不能为 null，默认值为字符串 beijing，且该列位于原表 c_sex 列之后。

mysql> alter table mysql_test.customers 
    -> add column c_city char(50) not null default 'beijing' after c_sex;

修改列的名称或数据类型

将 c_sex 重命名为 sex

mysql> alter table mysql_test.customers
    -> change column c_sex sex char(3) null default 'm';

修改默认值

修改 c_city 的默认值为 shanghai

mysql> alter table mysql_test.customers
    -> alter column c_city set default 'shanghai';

修改数据类型

修改 c_name 为 char(20) 类型，并且放在第一列。

mysql> alter table mysql_test.customer
    -> modify column c_name char(20) first;

删除列

mysql> alter table mysql_test.customers
    -> drop column c_contact;

更改表名

修改表明 customers 为 cust

mysql> alter table mysql_test.customers
    -> rename to mysql_test.cust;

删除表

drop table if exists mysql_test.cust

查看表

show columns from mysql_test.cust;

describe mysql_test.cust;

desc mysql_test.cust;

+-----------+----------+------+-----+----------+----------------+
| Field     | Type     | Null | Key | Default  | Extra          |
+-----------+----------+------+-----+----------+----------------+
| c_id      | int(11)  | NO   | PRI | NULL     | auto_increment |
| c_name    | char(50) | NO   |     | NULL     |                |
| sex       | char(3)  | YES  |     | m        |                |
| c_city    | char(50) | NO   |     | shanghai |                |
| c_address | char(50) | YES  |     | NULL     |                |
+-----------+----------+------+-----+----------+----------------+

索引

在数据库 mysql_test 的表 cust 上，根据客户姓名列的前三个字符创建一个升序索引 index_cust。

mysql> create index index_cust
    -> on mysql_test.cust(c_name(3) asc);

在数据库 mysql_test 的表 cust 上，根据客户姓名列和客户id号创建一个组合索引 index_cust。

mysql> create index index_cust
    -> on mysql_test.cust(c_name, c_id);

显示索引

show {index | indexes | keys} {from | in} table_name [{from | in} db_name] [where expr]

show indexes from mysql_test.cust;

删除索引

drop index index_cust on mysql_test.cust;

mysql> alter table mysql_test.cust
    -> drop index index_cust22;

数据更新

插入数据（values）

mysql> insert into mysql_test.cust 
    -> values(900, 'zhangsan', 'F', 'beijing', 'chaoyang');

mysql> insert into mysql_test.cust
    -> values(81012, 'lis2', 'f', 'shsenzhen', 'luoadadhu'), (9001, 'wangwu', 'm', 'xian', 'luosu');

id 为自增长，c_sex 为默认值，c_address 为null

mysql> insert into mysql_test.cust
    -> values(0, 'lisi', default, 'wuhan', null);

插入数据（set）

mysql> insert into mysql_test.cust
    -> set c_name='lin', c_city='shanghai', c_sex=default;

删除数据

mysql> delete from mysql_test.cust
    -> where c_name='lisi';

更新数据

mysql> update mysql_test.cust
    -> set c_name='wangwu', c_sex='f'
    -> where c_id=81015;

查询

mysql> select c_name, c_city, c_sex 
    -> from mysql_test.cust;

查看全部数据

select * from mysql_test.cust;

+------+----------+-------+----------+-----------+
| c_id | c_name   | c_sex | c_city   | c_address |
+------+----------+-------+----------+-----------+
|  800 | lisi     | f     | shenzhen | luohu     |
|  900 | zhangsan | F     | beijing  | chaoyang  |
+------+----------+-------+----------+-----------+

定义并使用列的别名

mysql> select c_name, c_address as dizhi
    -> from mysql_test.cust;

+----------+-----------+
| c_name   | dizhi     |
+----------+-----------+
| zhangsan | chaoyang  |
| wangwu   | dfdf      |
| lis2     | luoadadhu |
| lin      | NULL      |
| wangwu   | NULL      |
+----------+-----------+

替换查询结果集中的数据

查询数据库 mysql_test 的表 cust 中客户的 c_name 列和 c_sex 列，要求判断结果集中 c_sex 列的值，如果该列的值为 m，则显示输出“男”，否则为“女”，同时在结果集的显示中将 c_sex 列用别名“性别“标注。

mysql> select c_name,
    -> case 
    -> when c_sex='m' then '男'
    -> else '女'
    -> end as 性别
    -> from mysql_test.cust;

+----------+--------+
| c_name   | 性别   |
+----------+--------+
| zhangsan | 女     |
| wangwu   | 男     |
| lis2     | 女     |
| lin      | 男     |
| wangwu   | 女     |
+----------+--------+

查询数据库 mysql_test 的表 cust 中每个客户的 c_name 列、c_sex 列，以及对 c_id 列加上数字100后的值

mysql> select c_name, c_sex, c_id+100
    -> from mysql_test.cust;

+----------+-------+----------+
| c_name   | c_sex | c_id+100 |
+----------+-------+----------+
| zhangsan | F     |     1000 |
| wangwu   | m     |     9101 |
| lis2     | f     |    81112 |
| lin      | m     |    81114 |
| wangwu   | f     |    81115 |
+----------+-------+----------+

聚合函数

count

交叉连接，又称笛卡尔积

select * from tab1 cross join tab2;

select * from tab1, tab2;

内链接

select c_num from tab1 inner join tab2 on tab1.c_num < tab2.c_num2;

左外链接

select * from tab1 left outer join tab2 on tab1.c_num < tab2.c_num2;

select * from tab1 left join tab2 on tab1.c_num < tab2.c_num2;

右外链接

select * from tab1 right outer join tab2 on tab1.c_num < tab2.c_num2;

select * from tab1 right outer join tab2 on tab1.c_num < tab2.c_num2;

比较运算

mysql> select * from mysql_test.cust
    -> where c_sex='m';

判定范围

between

select * from mysql_test.cust where c_id between 9000 and 9005;

in

mysql> select * from mysql_test.cust
    -> where c_id in (900, 9001, 81015);

+-------+----------+-------+----------+-----------+
| c_id  | c_name   | c_sex | c_city   | c_address |
+-------+----------+-------+----------+-----------+
|   900 | zhangsan | F     | beijing  | chaoyang  |
|  9001 | wangwu   | m     | admk     | dfdf      |
| 81015 | wangwu   | f     | shanghai | NULL      |
+-------+----------+-------+----------+-----------+

判定空值

is

mysql> select * from mysql_test.cust
    -> where c_address is not null;

mysql> select c_name, c_address, c_sex from mysql_test.cust
    -> where c_address is null;

子查询

mysql> select s_no, s_name
    -> from student_table
    -> where s_no in (select s_no from score_table where score > 80);

group by

mysql> select c_address, c_sex, count(*) as '人数'
    -> from mysql_test.user_table
    -> group by c_address, c_sex;

+-----------+-------+--------+
| c_address | c_sex | 人数   |
+-----------+-------+--------+
| NULL      | 0     |      1 |
| NULL      | 1     |      2 |
| beijing   | 0     |      1 |
| shanghai  | 0     |      2 |
+-----------+-------+--------+

汇总 with rollup

select c_address, c_sex, count(*) as '人数' from mysql_test.user_table group by c_address, c_sex with rollup;

+-----------+-------+--------+
| c_address | c_sex | 人数   |
+-----------+-------+--------+
| NULL      | 0     |      1 |
| NULL      | 1     |      2 |
| NULL      | NULL  |      3 |
| beijing   | 0     |      1 |
| beijing   | NULL  |      1 |
| shanghai  | 0     |      2 |
| shanghai  | NULL  |      2 |
| NULL      | NULL  |      6 |
+-----------+-------+--------+

having

mysql> select c_name, c_address
    -> from mysql_test.user_table
    -> group by c_name, c_address
    -> having count(*) < 3;

+----------+-----------+
| c_name   | c_address |
+----------+-----------+
| lisi     | NULL      |
| lisi     | shanghai  |
| lisiss   | NULL      |
| zhangsan | beijing   |
+----------+-----------+

order by

select c_name, c_sex from mysql_test.user_table order by c_name desc, c_address desc;

limit

select c_id, c_name from mysql_test.user_table limit 2, 2;

select c_id, c_name from mysql_test.user_table order by c_id limit 2, 3;

select c_id, c_name from mysql_test.user_table order by c_id limit 3 offset 2;

mysql> select * from 图书
    -> where 单价 between 50 and 60
    -> order by 出版社, 单价;

mysql> select 书名, 借阅时间
    -> from 图书表, 借阅表, 读者表
    -> where 姓名='张三' and 读者.借书证号=借阅表.借书证 and 借阅表.图书编号=书=图书表.图书编号

mysql> select 出版社, max(单价), min(单价), avg(单价)
    -> from 图书表
    -> group by 出版社;

视图

创建视图

mysql> create or replace view mysql_test.user_table_view
    -> as
    -> select * from mysql_test.user_table
    -> where c_sex='0'
    -> with check option;

查询视图

select * from mysql_test.user_table_view;

删除视图

drop view if exists mysql_test.user_table_view;

修改视图

mysql> alter view mysql_test.user_table_view
    -> as
    -> select * from mysql_test.user_table
    -> where c_sex='1'
    -> with check option;

show

show create view mysql_test.user_table_view;

insert

insert into mysql_test.user_table_view values(200, 'lis', '1', 'wh', 'bj');

update

mysql> update mysql_test.user_table_view
    -> set c_address='sh';

delete

delete from mysql_test.user_table_view where c_name='lis';

select

select c_name, c_address from mysql_test.user_table_view where c_id='103';

存储过程

use mysql_test

delimiter $$

创建

mysql> create procedure sp_update_sex(in cid int, in csex char(10))
    -> begin 
    -> update user_table set c_sex=csex where c_id=cid;
    -> end $$

调用存储过程

call sp_update_sex(100, 1) $$

删除存储过程

drop procedure if exists sp_update_sex $$

存储过程体

只能在存储过程体的begin...end语句块中声明
必须在存储过程的开头处声明
作用范围仅限于声明它的

声明一个整型局部变量cid

declare cid int(10);

使用set语句为局部变量赋值

set cid=900;

流程控制语句
条件判断语句
if...then...else语句
case语句
循环语句
while语句
repeat语句
loop语句

存储函数

创建存储函数

use mysql_test;

delimiter $$

mysql> create function func_search(cid int)
    -> returns char(20)
    -> deterministic
    -> begin
    -> declare sex char(20);
    -> select c_sex into sex from user_table where c_id=cid;
    -> if sex is null then
    -> return(select'meiyou kehu');
    -> else if sex='1' then
    -> return(select'nv');
    -> else return(select'nan');
    -> end if;
    -> end if;
    -> end $$

create function func_search(cid int) returns char(20) deterministic begin declare sex char(20); select c_sex into sex from user_table where c_id=cid; if sex is null then return(select'meiyou kehu'); else
if sex='1' then return(select'nv'); else return(select'nan'); end if; end if; end $$

调用

select func_search(102) $$

+-------------------+
| func_search3(102) |
+-------------------+
| nan               |
+-------------------+

触发器

insert触发器

创建

create trigger user_table_trigger after insert on user_table for each row set @str = 'one user_table added';

create trigger user_table_triger after insert on user_table for each row set @str=new.c_id

查看触发器

select @str

删除触发器

drop trigger if exists user_table_trigger

delete触发器

update触发器

create trigger mysql_test.user_table_update_trigger before update on mysql_test.user_table for each row set new.c_address=old.c_contact;

用户管理账号

创建用户

create user 'zhangsan'@'localhost' identified by '123';

select password(456)

create user 'lisi'@'localhost' identified by password '456'

删除用户账号

drop user 'zhangsan'@'localhost';

修改用户账号

rename user 'zhangsan'@'localhost' to 'new_zhangsan'@'localhost';

set password for 'zhangsan'@'localhost' = password('new_pass');

用户授权

已存在用户授权

grant select (c_id, c_name) on mysql_test.user_table to 'zhangsan'@'localhost'

新创建用户并授权

grant select, update on mysql_test.user_table to 'lisi'@'localhost' identified by 'password', 'wangwu'@'localhost' identified by '123';

所有数据库权限

grant all on mysql_test.* to 'zhangsan'@'localhost';

创建用户的权限

grant create user on *.* to 'zhangsan'@'localhost';

允许将自身的这个权限授予给其他用户

grant select, update on mysql_test.user_table to 'zhangsan'@'localhost' identified by '123' with grant option;

撤销权限

revoke select on mysql_test.user_table from 'zhangsan'@'localhost';

数据库系统原理 SQL 杂记

索引

数据更新

查询

子查询

视图

存储过程

存储过程体

存储函数

触发器

insert触发器

delete触发器

update触发器

用户管理账号

撤销权限

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读