SQL 语言
1. 概括
? SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关 系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
? 各数据库厂商都支持ISO的SQL标准，普通话
? 各数据库厂商在标准的基础上做了自己的扩展，方言
? SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容， 并删除条目等操作。
? Create, Read, Update, and Delete 通常称为CRUD操作。
2. SQL 语句分类
- DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
- DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
- DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
- DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。
注意：sql语句以 “;” 结尾
mysql中的关键字不区分大小写
3. DDL 操作数据库
3.1. 创建
CREATE DATABASE语句用于创建新的数据库：
编码方式:gb2312,utf-8,gbk,iso-8859-1
// create database 数据库名
CREATE DATABASE mydb1;
// create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
// create database 数据库名 set 编码方式 collate 排序规则
CREATE DATEBASE mydb3 character set GBK COLLATE gbk_chinese_ci;
3.2. 查看数据库
查看当前服务器中所有的数据库
show databases;
查看前面创建的mydb2 数据库的定义信息
// show create database 数据库名;
Show CREATE DATABASE mysql2;
3.3. 修改数据库
alter databse 数据库名 character set 编码方式;
查看服务器中的数据库，并把mydb2 的字符集修改为utf8；
alter DATABASE mydb2 character SET uft8;
3.4. 删除数据库
drop database 数据库名
drop database mysql3;
3.5. 其他语句
Select database();
切换数据库： use 数据库名
USE mydb2;
4. DDL操作表
CREATE TABLE 语句用于创建新表。
语法：
CREATE TABLE 表名（
? 列名1 数据类型 [约束],
? 列名2 数据类型 [约束],
? 列名n 数据类型 [约束]
）;
说明：表名，列名是自定义，多列之间使用逗号间隔，最后一列逗号不能写
? [约束] 表示可有可无
示例：
CREATE TABLE Employees(
? id INT,
? age INT,
? first VARCHAR(255),
? last VARCHAR(255)
);
常用的数据类型：
? int: 整型
? double: 浮点型，例如double(5,2) 表示最多5位，其中必须有2位小数，即最大值为999.99;默认支持四舍五入
? char: 固定长度字符串类型；char(10) 'aaa ’ 占10个字符
? varchar: 可变长度字符串类型； varchar(10) ‘aaa’ 占3个字符
? text : 字符串类型，比如小说信息；
? blob : 字节类型，保存文件信息(视频，音频，图片)；
? date : 日期类型，格式为：yyyy-MM-dd;
? time : 时间类型，格式为： hh:mm:ss
? timestamp: 时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
? datatime: 日期时间类型 yyyy-MM-dd hh:mm:ss
4.1 其他表操作
drop table 表名；
DROP TABLE table name;
当前数据库中所有的表
show tables;
查看表的字段信息
DESC employee;
增加列： 在上面员工表的基本上增加一个image列。
alter table 表名 add 新列名 新的数据类型
alter table employee ADD image blob;
修改job 列，使其长度为 60。
alter table 表名 change 旧列名 新列名 新的数据类型
ALTER TABLE employee MODIFY job varchar(60);
ALTER TABLE employee change job job varchar(60);
列名name 修改为username
ALTER TABLE employee change name username varchar(100);
删除image列，一次只能删除一行
alter table Employee drop image;
修改表名，表名改为 user。
alter table 旧表名 rename 新表名;
alter table employee rename user;
查看表格的创建细节
show create table 表名;
show create table user;
修改表的字符集为gbk
alter table 表名 character set 编码方式
alter table user character set gbk;
练习一下
表名 card(会员卡表)
列名 数据类型
cardid int
cardnum varchar(20)
regDate date
需求：（注意 要管理员权限运行 cmd，否则有可能因为权限不够，命令失败）
- 创建该表
- 将表名 从 card 改为 CardInfo
- 添加 delDate(注销时间)列 添加到表中
- 将cardnum 改为varchar(30)
- 删除 regDate 列
- 删除 cardInfo表
5. DML 操作
DML是对表中的数据进行增、删、改的操作。不要和DDL混淆额。
主要包括，INSERT、UPDATE、DELETE
小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。
空值：null
(1) 插入操作：INSERT：
insert into 表名(列名) values(数据值);
insert into student(stuname,stuage,stusex,birthday) values('张三1',18,'a','2000-1-1');
– 注意： 1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应。
– 非数值的列值两侧需要加单引号
– 添加数据的时候可以将列名省略->当给所有列添加数据的时候
– 此时列值的顺序按照数据表中列的顺序执行
insert into student values(‘李四’,12,‘1111’,189.98,‘2000-1-1’,‘男’,‘2007-1-1’);
– 同时添加多行
insert into 表名(列名) values(第一行数据),(第二行数据),(),();
insert into student(stuname,stuage,stusex,birthday)
values('张三1',18,'a','2000-1-1'),
('张三2',18,'a','2000-1-1'),
('张三3',18,'a','2000-1-1'),
('张三4',18,'a','2000-1-1'),
('张三5',18,'a','2000-1-1'),
('张三6',18,'a','2000-1-1');
注意：列名与列值的类型、个数、顺序要一一对应。
? 参数值不要超过定义的长度。
? 如果插入空值，请使用null
? 插入的日期和字符一样，都使用引号括起来。
练习准备：
直接用下面代码，去生成一个emp 表
create table emp(
id int primary key,
name varchar(100) not null,
gender varchar(10) not null,
birthday date,
salary float(10,2),
entry_date date,
resume text
);
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','goodgirl');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,'你好','male','1995-5-10',10000,'2015-5-5','good boy');
(2) 更新操作 update：
语法: UPDATE 表名 SET 列名1=列值1，列名2=列值2…WHERE 列名=值
练习：
将所有员工的薪水修改为5000元
将姓名为’zs’的员工薪水修改为3000元
将姓名为’aaa’的员工薪水修改为4000元，resume改为ccc。
将wu的薪水在原有基础上增加1000元
答案：emp 表下
update emp set salary=5000;
update emp set salary=3000 where name=‘zs’;
update emp set salary=4000,resume=‘ccc’ where name=‘aaa’;
update emp set salary=salary+1000 where name=‘wu’;
(3) 删除操作：DELETE:
语法：DELETE from 表名 [WHERE 列名=值]
练习：
删除表中名称为’zs’的记录。
删除表中所有记录
使用truncate删除表中记录
truncate table emp;
- DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
- TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
- 删除的数据不能找回。执行速度比DELETE快。
练习题:
Manager(管理员表)：
? mid 编号 int （主键）
? mname 名字 varchar(20)
? age 年龄 int
? sex 性别 char(2)
? password 密码 varchar(20)
? address 地址 varchar(20)
? phone 电话 varchar(20)
数据：
1 王子 18 男 123 北京 110
2 公主 20 女 456 上海 220
3 太子 23 男 789 南京 330
需求：
(1)创建表
(2)将数据插入到表中
(3)将王子的年龄修改为24
(4)将地址是南京的管理员改为天津
(5)将性别是女,并且年龄大于30的用户密码改为888888
(6)将所有用户的密码恢复最初设置111111
(7)将员工的电话中不是110的电话号码改为7654321
(8)将王子的年龄改为18，地址改为承德，性别改为女
(9) 删除王子的信息
(10)删除地址在南京并且年龄大于60的员工信息
(11)删除不在北京的员工信息
(12)删除地址在北京或上海的员工信息
(13)删除电话号码是空的员工信息
小结: 为空的条件：列名 is null or 列名=’’
注:两个单引号表示空字符串
日期类型值的区别:
? date：yyyy-MM-dd （年月日）
? time：hh:mm:ss (时分秒)
? datetime:yyyy-MM-dd hh:mm:ss (年月日时分秒)
? 获取当前系统时间:now()
select now();
6.DCL
1. 创建用户
create user 用户名@指定ip identified by 密码;
create user test123@loaclhost IDENTIFIED by 'test123';
create user 用户名@指定ip identified by 密码； 指定ip才能登陆
create user test456@10.4.50.129 IDENTIFIED by 'test456';
create user 用户名@’%’ identified by 密码 任意IP 均可以登录
create user test789@'%' IDENTIFIED by 'test7'
2. 用户授权：
grant 权限1,权限2,…,权限n on
数据库名.* to 用户名@ip; 给指定用户授予指定 指定数据库 指定权限
grant select,insert,update,delete,create on
chaoshi.* to 'test456'@'127.0.0.1';
grant all on . to 用户名@IP 给指定用户授权所有数据库所有权限
grant all on *.* to 'test456'@'127.0.0.1';
3.用户权限查询：
show grants for 用户名@ip;
show grants for 'root'@'%';
4. 撤销用户权限：
revoke 权限1,权限2,权限3,…,权限n, on 数据库名.* from 用户名@ip;
revoke select on *.* from 'root'@'%';
5.删除用户：
drop user 用户名@IP；
drop user test123@localhost;
6.查看所有用户
根据用户名和host地址查看当前所有用户
select user,host from mysql.user;
|