mysql练习

Mysql基本操作

1. mysql 授权

语法:

　　用户管理

创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址' to '新用户明'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码');

　　权限管理

查看用户权限
show grants for '用户名'@'IP地址';
授权
grant 权限 on 数据库.表 '用户名'@'IP地址';
取消权限
revoke 权限 on 数据库.表 from '用户名'@ip地址;

　　创建 lisi 用户,密码为 '123456',只允许在192.168.10.0网段登录; 创建 xiaohong用户 密码为 '456789',只允许在本地登录;

> create user 'lisi'@'192.168.10.%' identified by '123456';
> create user 'xiaohong'@'localhost' identified by '456789';

　　授权 lisi 用户管理db1数据库,权限为 all; 授权小红用户管理 db1 权限为,更新,插入,创建,查询.

> grant all privileges on db1.* to 'lisi'@'192.168.10.%';
> grant update,select,insert,create on db1.* to 'xiaohong'@'localhost';

　　查看xiaohong的权限

> show grants for 'xiaohong'@'localhost';

　　修改xiaohong 密码为:'xiaohong',回收对 db1数据库的 更新权限

> set password for 'xiaohong'@'localhost' = Password('xiaohong');
> revoke update on db1.* from 'xiaohong'@'localhost';

 2. Sql语句

数据库操作

语法
    创建数据库
        create databas <检测语句> 库名 <默认字符集> <默认排序规则>;
    查看数据库
        show databases;
    删除数据库
        drop database 库名;

# 创建数据库设置 字符编码为utf8 排序规则为utf8_general_ci ;
> create database if not exists test3 default charset utf8 collate utf8_general_ci;

# 排序规则
utf8_general_ci :不区分大小写,
utf8_general_cs : 区分大小写
utf8_bin: 字符串每个字符串用二进制数据编译存储;
utf8_unicode_ci 和 utf8_general_ci 类似
utf8_general_ci校对速度快，但准确度稍差
utf8_unicode_ci准确度高，但校对速度稍慢。

表操作　　

　创建表系列:

# 建表语法
    create table 表名字(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#  在同一张表中,字段名字不能重复
# 宽度和约束条件可选
# 字段名和类型是必须的

　　数据库约束

not null: 非空约束,指此列不能为空

null　　可以为空

default : 设置值约束,指某段的默认值

unique: 为一件约束,指定某列或者激烈组合不能重复

auto_increment: 自增约束,指定类型为int,非空列自动增加

primary key: 主键, 指定该列的值可以唯一的表示该列记录

foreignkey: 外检,指定改行记录从属于主表中的一条记录,

　　数据类型数值类型

tinyint     (-128,127)    
smallint    (-32768,32767)
mediumint    (-8 388 608，8 388 607)
int    (-2147483648，2147483647)
bigint ((-9 233 372 036 854 775 808，9 223 372 036 854 775 807))
float     (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)     # 浮点数


时间类型
DATE	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME    	'-838:59:59'/'838:59:59'	HH:MM:SS	时分秒
YEAR        1901/2155	                 YYYY	        年份值
DATETIME	    1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	年月日时分秒 # 常用  

# 字符串类型
类型	                大小    	        用途
CHAR	    0-255字节	        定长字符串
VARCHAR	    0-65535 字节	变长字符串
TINYBLOB	    0-255字节	        不超过 255 个字符的二进制字符串
TINYTEXT	    0-255字节	        短文本字符串
BLOB	    0-65 535字节	二进制形式的长文本数据
TEXT	            0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	    二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	    中等长度文本数据
LONGBLOB	0-4 294 967 295字节    二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节    极大文本数据 

枚举和集合
ENUM : 枚举,他的取值范围需要在创建表时 通过枚举方式显示,ENUM 只允许从值集合中选取单个值,二不能一次取多个值
SER : 集合 和 ENUM 相似, 字符串对象,里面可以包含 0-64 个成员, 根据成员的不同,存储上也有所不同, ser类型可以允许值
集合中人鱼选择1或多个元素,对超出范围的内容将不允许注入,对重复的值进行自动去重

　　建表实验

　　1. 创建 students表, id int类型 段为自增 主键 不能为空, name 为 类型为char(20) 不能重复 不能为空,age int(3) 不能为空, gender 为枚举(female,male) 默认为male,height 为 浮点类型 一共为四位,保留一位小数,data 类型为 datatime ,remarks 为 varcher500类型.

> create table students(id int auto_increment primary key not null,
name char(20) unique not null,
age int(3) not null,
gender enum('female','male') default 'female',    -> height float(4,1),
data datetime,
remarks varchar(5000));

修改表结构 alter

1 修改表名
alter table 表名 rename 新表名;

2. 增加字段
alter table 表名 
                add 字段名 表数据类型 [约束条件],
                add 字段名 表数据类型 [约束条件];   
3. 修改字段
alter table 表名
                modify 字段名 数据类型[完整条件约束];
alter table 表名 
                change 旧字段名 新字段名 旧数据类型 [约束条件];
4. 删除字段
    alter table 表名 drop 字段名;
5. 修改字段排序
alter table 表名
    add 字段名 数据类型 [约束条件] first; #添加字段排在第一位

alter table 表名
    add 字段名 数据类型 [约束条件] after 字段名; 添加字段在某个字段之后

alter table 表名
    change 字段名 旧字段名 新字段名 新数据类型 [约束] first;

alter table 表名
    modify 字段名 数据类型 [约束] after 字段名;

列子:
create table t(id int unique,name char(10) not null);
# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;


# 例子
# 添加列
mysql> alter table staff add sex enum('male','female');
# 修改id的宽度
mysql> alter table staff modify id int(4);
# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);

删除表系列

delete table 表名字;    清空表,但是不能清空自增计数
truncate table 表名;    清空表,同时清空自增计数,速度快
drop table 表名;        删除表
delete from table 表名 where 条件;删除数据

查看表结构以及建表语句

MariaDB [test3]> desc students;
+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| id      | int(11)               | NO   | PRI | NULL    | auto_increment |
| name    | char(20)              | NO   | UNI | NULL    |                |
| age     | int(3)                | NO   |     | NULL    |                |
| gender  | enum('female','male') | YES  |     | female  |                |
| height  | float(4,1)            | YES  |     | NULL    |                |
| data    | datetime              | YES  |     | NULL    |                |
| remarks | varchar(5000)         | YES  |     | NULL    |                |
+---------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

MariaDB [test3]> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` int(3) NOT NULL,
  `gender` enum('female','male') DEFAULT 'female',
  `height` float(4,1) DEFAULT NULL,
  `data` datetime DEFAULT NULL,
  `remarks` varchar(5000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 表的数据插入系列 inster

语法
insert into 表名(字段1,字段2,字段3....) values (值1,值2,值3....);
insert into 表名 values (值1,值2,值3....);
insert into 表名 values (值1,值2,值3....),(值1,值2,值3....),(值1,值2,值3....);

表的数据更新 update

语法
语法：
   update 表名 set
        字段1=值1,
        字段2=值2,
        where CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;