Linux下数据库学习过程之库表操作（持续更新中）

1.库表操作

注释

多行注释：/* .....*/
单行：--
 mysql注释：#

通配符：wildcard

%:任意多个任意字符
_：单个任意字符	
show databases like '%schema'；  查找数据库名字以schema结尾的数据库

库操作

创建数据库
create database mydb; 
创建数据库，如果不存在则创建
create database IF NOT EXISTS mydb; 
查看警告
show warnings; 
删除数据库，如果存在则删除
drop 	database IF EXISTS mydb；
查看当前使用的数据库
select database（）；
选择使用数据库
use mydb；mydb是数据库名
查看数据库版本
select version（）；
查看当前用户
select user（）；	
查看用户，主机，密码
mysql> select user,host,authentication_string from mysql.user;
查看时间
system date；

字符集

查看字符集（模糊匹配）
show variables like '%character%';
未完待续...

表操作

可以指定字符集，默认使用数据库所使用的字符集
varchar：可变长度的字符，varchar（100），存str，长度即为3，而如果用char，则长度为100
text：文本，大容量的字符串，多种类型，未完待续...
BLOB：二进制文本，单位：字节，未完待续...
ENUM：枚举
SET：集合
数字类型：int，double，float，decimal
date类型

create table 数据库名.表名{
	列名  数据类型  约束（限定条件） comment ‘’，
	列名
	...
	列名n ...
}存储引擎  字符集   校对规则

先创建一个简单的表
mysql> create table stu_info(
    ->   stu_id int NOT NULL comment '学生编号',
    ->   stu_name varchar(32) comment '学生姓名'
    -> )engine=InnoDb character set gbk collate gbk_chinese_ci;

[root@db ~]# cat /etc/my.cnf   查看数据库存储的目录
mysql> insert into stu_info values(1001,'张三'),(1002,'李四');   插入信息
mysql> select *from stu_info;   查询表内数据
mysql> show tables;  查询数据库包含的表
mysql> desc stu_info;  查看表结构
mysql> show columns from stu_info; 查看表结构
mysql> drop table stu_info;  删除表
mysql> alter table stu_info add stu_gender char(1);  修改表结构。增加列
mysql> alter table stu_info modify gender enum('M','F'); 修改列的类型 
mysql> alter table stu_info drop gender;  删除列
mysql> alter table stu_info rename stu_informaion; 修改表名
mysql> alter table stu_info change gender stu_gender char(1);  修改列名
mysql> create table stu_info2 like stu_info; 复制表结构创建新表。但不包括表数据
mysql> create table stu_info3 select * from stu_info;   复制表结构。包括表数据。"*"表示查询所有的列

约束

创建两个表，语句包含约束

mysql> create table class( class_id int primary key auto_increment comment '班级编号', class_name varchar(64) not null )auto_increment=1001;    创建班级表，并且插入两条数据，班级编号分别为1001和1002
创建学生表
create table student(
 	stu_id int primary key auto_increment comment '学号',    主键
 	stu_name varchar(32) not null comment '姓名',             不为空
 	stu_age tinyint check (stu_age >= 18) comment '年龄',    年龄大于等于18，check检查
    stu_gender char(1) default 'M' check (stu_gender in ('M','F')) comment '性别',     默认值
    stu_addr varchar(255) unique,      唯一键  
    stu_class int,    设置外键
    foreign key (stu_class) references class(class_id) 
)auto_increment=1100001;   
mysql-> show create table student;  展示创建表的语句

测试约束

mysql> insert into student values(null, 'zhangsan',18,'M','shannxi',1001); 
mysql> insert into student values(null, 'zhangsan',18,'M','shanxi',1002);
mysql> insert into student values(null, 'zhangsan',18,'M','shanxi',1001);
这三条插入数据展示主键约束，以及唯一键约束。测试结果：主键因设置了递增，所以为空不影响数据的插入。而唯一键的内容必须不同，而且发现插入数据时，唯一键与上一条数据相同，那么插入失败，但主键的递增会保留，也就是说即使插入失败，主键的递增也不会受到影响。感觉是个bug，！！！！！在这插个眼！！！！！！！！！！！！
mysql> insert into student values(null,null,18,'M','shdanxdybi',1001);   这条插入测试姓名的不为空，测试结果：插入失败
mysql> insert into student values(null,'lisi',14,'M','shdsanxdybi',1001);   这条测试年龄check，插入失败
mysql> insert into student values(null,'lisi',18,'' ,'sshddsanxdybi',1001);   插入失败，这里测试了性别是否可以不设置就选择默认值，测试结果是没通过check2
mysql> insert into student(stu_id,stu_name,stu_age,stu_addr,stu_class) values(null,'lisi',18,'sshddsanxdybi',1001);插入成功，这里测试性别不设置选择默认值
mysql> insert into student values(null,'lisi',18,'F' ,'sshdsdsanxddybi',1003);测试外键的关联性，班级表没有编号为1003的。所以插入失败。

创建新表，语句不包含约束，通过修改表结构添加约束

mysql> create table student2(
    -> stu_id int comment '学号',
    -> stu_name varchar(32) comment '姓名',
    -> stu_age tinyint,
    -> stu_gender char(1),
    -> stu_addr varchar(255),
    -> stu_class int
    -> );
修改
    mysql> alter table student2 add constraint stu_id_pk primary key(stu_id); 添加主键
    mysql> alter table student modify stu_name varchar(32) not null;  设置不为空
    mysql> alter table student2 add constraint stu_chk_1 check(stu_age >= 18); 设置check约束
    mysql> alter table student2 modify stu_gender char(1) default 'M'; 设置默认值
	mysql> alter table student2 add constraint stu_chk_2 check(stu_gender in ('M','F'));  设置check约束
	mysql> alter table student2 add constraint stu_addr_key unique key(stu_addr);唯一键约束
	mysql> alter table student2 add constraint stu_class_class_id_fk foreign key(stu_class) references class(class_id);   设置外键
-------------------------------------
删除约束
	mysql> alter table student2 drop primary key;
	mysql> alter table  student2 modify stu_name varchar(32);
	mysql> alter table student2 drop constraint stu_chk_1;	
	mysql> alter table student2 modify stu_gender char(1);
	mysql> alter table student2 drop constraint stu_chk_2;
    mysql> alter table student2 drop constraint stu_addr_key;
    mysql> alter table student2 drop constraint stu_class_class_id_fk;
    mysql> alter table student2 drop key stu_class_class_id_fk;

授权

密码相关（临时修改）
mysql> show variables like '%validate_password%';  查看密码策略
mysql> set global validate_password.policy=0;  设置策略为最低等级
mysql> set global validate_password.length=0; 设置密码长度为0，但尝试结果是无论策略等级是什么，密码长度最低为4
----------------------------------------------------------------------
授权
mysql> create user testuser1@'%' identified by '123456',testuser2@'%' identified by '123456';创建两个用户
mysql> select host,user from mysql.user;查找创建的用户保存在什么地方
mysql> desc mysql.user;查看.....
mysql> select host,user,authentication_string from mysql.user;  缩小查看内容
mysql> show grants for testuser1;  查看用户权限
mysql> grant select,insert,update,delete,create,alter,drop on mydb.* to testuser1@'%';  root下操作，给用户授权；*（库）.*（表）
mysql> flush privileges;刷新授权
mysql> show grants for testuser1\G;  查看用户被允许的权力
mysql> revoke create,drop,alter on mydb.* from testuser1@'%';  收回权限
mysql> flush privileges;刷新授权  刷新后，用户需重新登录才能刷新成功
mysql> grant all privileges on *.* to testuser2@'%';  给用户2授权全部权限（一般不会给普通用户授予全部权力）
mysql> flush privileges;刷新授权