简单SQL语句

Posted by

SQL语句

#删除abc@'%'用户
mysql> drop user abc@'%';

#查看时间
mysql> select curtime();

#查看当前库
mysql> select database();

#查看所有用户的权限
mysql> select * from mysql.user\G

#查看当前实例的server_id
mysql> show variables like 'server_id';

#lower_case_table_names=0时不区分大小写(最好不使用,否则可能会导致删错库)
    若想使得其区分大小写需在my.cnf在配置lower_case_table_names = 1 并重启mysql
mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

#初始化实例:指定用户为mysql  指定配置文件为/data/3307/my.cnf 指定程序目录为/application/mysql  指定数据目录为/data/3307/data
./mysql_install_db --user=mysql --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data

指定文件启动数据库
mysqld_safe --defaults-file=/data/3307/my.cnf &

修改root用户localhost主机域的密码为1

#主机update在做修改密码的操作后需要flush刷新
update mysql.user set password=PASSWORD('1') where user='root' and host='localhost';

#命令行修改密码
mysqladmin -uroot -p1 password '123'

mysqladmin -u用户 -h 主机域 -p原始密码 password '新密码'

#set修改密码,是该当前登录的用户
mysql> set password=PASSWORD('789')

mysql> grant all on *.* to root@'localhost' identified by '123';

#刷新
mysql> flush privileges;

DDL:数据定义语言(create drop alter)

库对象:库名字、库属性(开发规范:库名小写)

创建库:create database|schema

语法:create database 库名字;
#创建数据库oldboy
mysql> create database oldboy;

#查看数据库
mysql> show databases;

#查看其创建语句(DQL)
mysql> show create database oldboy;

#创建abc库,并添加属性(这里是字符集)
mysql> create database abc charset gbk;

删库:drop database

#删除abc库
mysql> drop database abc;

修改数据库:alter database

语法:alter database 库名 属性 类型;

#修改oldboy数据库属性
mysql> alter database oldboy charset gbk;

#查看oldboy的创建与语句(DQL)
mysql> show create database oldboy;

表对象:列名、列属性、约束

创建表:create table

语法:create table 表名(列名 类型 数据约束 注释);

mysql> create table student(
    -> id int not null primary key auto_increment comment '学号',
    -> name varchar(10) not null comment '姓名',
    -> age tinyint unsigned comment '年龄',
    -> sex enum('f','m') not null default 'm' comment '性别',
    -> date datetime not null default NOW() comment '入学时间');
Query OK, 0 rows affected (0.04 sec)

规范的表结构 = 列名,类型,数据约束,注释

记得测试一下自己加ID为5,然后看他从3自增到6,看到5会不会报错
结果:会从5开始再计数

#查看表结构
mysql> desc student;
查看表内容
mysql> select * from student;
可以查看到注释内容
mysql> show create table student;
utf8中一个中文占3个字符
utf8mb4中一个中文占4个字符
数据类型:
char  字符类型(定长)
varcahr   字符类型(变长)
int       整型
tinyint   -128~128(最大127,128不行) 255个数
enum  枚举类型
datetime  时间类型 年月日时分秒

数据属性:
not null:非空
primary key:主键(唯一且非空的)
auto_increment:自增(此列必须是:primary key或者unique key)
unique key: 单独的唯一的        unique key + not null = primary key
default: 默认值
unsigned: 非负数
comment: 注释

删除表

语法:drop table 表名;

#删除表
mysql> drop table abc;

修改表的定义:alter table

语法:

​ alter table 表名 add(添加) 列名 类型 数据约束 注释 first(表首)|int after (指定在某列后面);

​ alter table modify(修改属性) 列名 类型 数据约束 注释;

​ alter table change(修改列名及属性) 旧列名 列名 类型 数据约束 注释;

​ alter table 旧表名 rename 表名;

#添加列和列定义(添加在末尾)
mysql> alter table stu add age int;

#添加多个列
mysql> alter table stu add test varchar(20), add qq int;

#添加列(表首)
mysql> alter table stu add iphone int first;

#指定位置进行添加(指定列,指定添加ccc到iphone后面)
mysql> alter table stu add ccc int after iphone;

#删除指定的列及定义
mysql> alter table stu drop age;

#修改列及定义(列属性)
mysql> alter table stu modify iphone varchar(20);

#修改列及定义(列名及属性)(把ccc列名替换为blog,类型替换为int)
mysql> alter table stu change ccc blog int;

#修改表名为test
mysql> alter table stu rename test;

DCL:数据控制语言(grant revoke)

针对权限进行控制

语法:grant 权限 on 库.表 to 用户@主机域 identified by ‘密码’

#授权oldboy@%用户所有权限(非超级管理员)
mysql> grant all on *.* to oldboy@'%' identified by '123';

#授权oldboy@%用户所有权限,且为超级管理员
mysql> grant all on *.* to oldboy@'%' identified by '123' with grant option;

#给wordpress@'10.0.0.5%'用户授权查询所有库所有表的权限,密码是123
    10.0.0.50-59 10.0.0.5
mysql> grant select on *.* to wordpress@'10.0.0.5%' identified by '123';

#给wordpress@'10.0.0.0.5%'用户授权对wordpress库下所有表有增,删,改权限,密码为123
mysql> grant insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';

#给wordpress@'10.0.0.5%'用户授权对wordpress库下的t1表所有权限,密码为123
mysql> grant all on wordpress.t1 to wordpress@'10.0.0.5%' identified by '123'

#脱敏:单列级别限制权限
mysql> grant select(user) on mysql.user to dev@'%' identified by '123';
mysql> grant select(user,host) on mysql.user to dev@'%' identified by '123';

#限定允许同时连接的终端数量
mysql> grant all on *.* to blog@'%' identified by '123' with max_user_connections 2;

#其他参数(扩展)
max_queries_per_hour:一个用户每小时可发出的查询数量
max_updates_per_hour:一个用户每小时可发出的更新数量
max_connections_per_hour:一个用户每小时可连接到服务器的次数
max_user_connections:允许同时连接数量

revoke

语法:revoke 权限 on 库.表 from 用户@主机域;

#收回select权限
mysql> revoke select on *.* from blog@'%';

#查看权限
mysql> show grants for blog@'%';

DML:数据操作语言(insert update delete)

操作表的数据信息

insert

语法:insert into 表名(列名) valuse(一一对应的值);

#基础用法,插入数据(必须按照表结构顺序一一对应的添加值)(不规范)
mysql> insert into student values(1,'aaa',15,'m',NOW());

#规范用法,插入数据
mysql> insert into student(name,age) values('bbb',16);

#插入多条数据
mysql> insert into student(name,age) values('ccc',16),('ddd',19);

update

语法:update 表名 set 列名=’值’ where 条件;

#不规范(不接条件判断,会导致所有的sex信息全部替换)
mysql> update student set sex='f';

#规范update修改
mysql> update student set sex='m' where id=3;
(修改多个)
mysql> update student set sex='m' where id=1 or id=4;
mysql> update student set sex='m' where id=1 or name='ccc';
mysql> update student set age=99 where id=3 and name='ccc';

#如果非要全表替换
mysql> update student set sex='f' where 1=1;

delete

语法:delete from 表名 where 条件;

#不规范
mysql> delete from abc;

#规范删除(危险)(自增的值,会接着增长)
mysql> delete from abc where id=3;

#DDL删除表内容(自增的值,会重新开始计算)
mysql> truncate table abc;

使用update来代替delete,做伪删除

#添加有个状态列
mysql> alter table student add state enum('1','0') default 1;

#使用update
mysql> update student set state='0' where id=2 or id=3;

#应用查询存在的数据
mysql> select * from student where state=1;

DQL:数据查询语言(select)

select:基础用法

语法:

​ select 列名 from 表名;

​ select 列名 from 表名 limit(顺序) 值;

​ select 列名 from 表名 desc limit(倒序) 值;

​ select 列名 from 表名 where 条件;

​ select 列名 from 表名 like(模糊查询) ‘值’;

#常用用法(查找city表中countrycode,district两列)
mysql> select countrycode,district from city;

#查询单列
mysql> select countrycode from city;

#行级查询(2:只显示符合条件的前两行数据;120,2:显示第120个符合条件的前两行数据)
mysql> select countrycode,district from city limit 2;
mysql> select id,countrycode,district from city limit 120,2;

#条件查询
mysql> select name,population from city where countrycode='CHN';

#多条件查询
mysql> select name,population from city where countrycode='CHN' and district='anhui';

#模糊查询(%H%:含义H的;%H:以H结尾的;H%:以H开头的)
mysql> select name,population,countrycode from city where countrycode like '%H%' limit 10;

#排序查询(顺序,查找city表中以population字段顺序排列的id,name,population,countrycode的片是个值得信息)
mysql> select id,name,population,countrycode from city order by population limit 10;

#排序查询(倒序,查找city表中以population字段倒序排列的id,name,population,countrycode的片是个值得信息)
mysql> select id,name,population,countrycode from city order by population desc limit 10;

#范围查询(>,<,>=,<=,<>)
mysql> select * from city where population>=1410000;

#范围查询OR语句(查询city表中满足countrycode='CHN'或者countrycode='USA'的所有值信息)
mysql> select * from city where countrycode='CHN' or countrycode='USA';

#范围查询IN语句(查询city表中满足countrycode='CHN'或'USA'的所有值信息)
mysql> select * from city where countrycode in ('CHN','USA');