MySQL基础-SQL命令和语言(一)
目录
一.MySQL命令
1.mysql中的快捷键
#最上面的最常用，
mysql> \h
\?或者（\h） #帮助
\c #终止当前命令
\d #修改命令结束符
\G #垂直显示结果
\q #退出数据库
\T #将执行过程与结果写入一个文件
\r #重连数据库
\e #写入文件，同时执行多条命令
\g #命令结束
\t #取消写入文件
\p #打印一次命令
\R #修改命令提示符
source (\.) #读取sql文件
status (\s) #查看数据库状态
system (\!) #在数据库里执行命令行命令
use (\u) #切换数据库
prompt (\R) Change your mysql prompt.
nopager (\n) Disable pager, print to stdout.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
2.mysql的help命令
#各种命令的语法使用
mysql> help
mysql> help contents
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show
3.客户端mysqladmin命令
1).修改密码
mysqladmin -uroot -p123 password 123456
2).关闭数据库
#一般多实例使用
mysqladmin -uroot -p123 -S /tmp/mysql.sock shutdown
3).建库
[root@db03 ~]# mysqladmin -uroot -p123456 create wordpress
4).删库
[root@db03 ~]# mysqladmin -uroot -p drop wordpress
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'wordpress' database [y/N] y
Database "wordpress" dropped
5).查看数据库配置
[root@db03 ~]# mysqladmin -uroot -p variables | grep server_id
Enter password:
| server_id | 4 |
| server_id_bits | 32 |
6).确认数据库是否启动
[root@db03 ~]# mysqladmin -uroot -p123 ping
Warning: Using a password on the command line interface can be insecure.
mysqld is alive
7).查看数据库信息
[root@db03 ~]# mysqladmin -uroot -p123 status
Warning: Using a password on the command line interface can be insecure.
Uptime: 143 Threads: 1 Questions: 22 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.153
8).刷新授权表
#相当于在数据库中执行 flush privileges
[root@db03 ~]# mysqladmin -uroot -p123 reload
9).刷新binlog
#如果有主从，不要刷新binlog，如果没有主从，可以先刷新binlog再做主从
[root@db03 ~]# mysqladmin -uroot -p123 flush-log
二.SQL语句
#**sql语句的语义种类**
DDL: 数据定义语言 Data Definition Language
DCL: 数据控制语言 Data Control Language
DML: 数据操作语言 Data Manipulate Language
DQL: 数据查询语言 Data Query Language
三.DDL:数据定义语言（create drop）
1.create针对库的操作
1).语法
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
2).创建库
mysql> create database db1(库名);
Query OK, 1 row affected (0.00 sec)
#建库时提示已存在，加上NOT EXISTS就不会报错
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
3).查看建库语句
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
4).创建库并指定字符集
mysql> create database db3 charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db3;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
#不指定校验规则默认就是 utf8_general_ci
mysql> create database db4 charset utf8;
Query OK, 1 row affected (0.00 sec)
5).修改库(字符集)
mysql> show create database db2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database db2 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
2.create针对表的操作
1).语法
mysql> help create table;
2).建表
#1.进入一个库
mysql> use db2
Database changed
#2.查看当前所在库
mysql> select database();
+------------+
| database() |
+------------+
| db2 |
+------------+
1 row in set (0.00 sec)
#3.建表，建表最少有一列
mysql> create table tb1;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
#4.查看表
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
3).数据类型
int 整数 -2^31 - 2^31-1 (-2147483648 - 2147483647)
tinyint 最小整数 -128 - 127 #年龄 0 - 255
varchar 字符类型（变长） #身份证
char 字符类型（定长）
enum 枚举类型 #给它固定选项，只能选则选择项中的值 性别 （如果enum 要用中文，那先确定表所在的库是utf8）
datetime 时间类型 年月日时分秒
4).建表测试
#1.建表
mysql> create table student(
-> id int,
-> name varchar(12),
-> age tinyint,
-> gender enum('M','F'),
-> cometime datetime);
Query OK, 0 rows affected (0.01 sec)
#2.插入一条数据
mysql> insert into student values(1,'张三',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
#3.查看数据
mysql> select * from student;
+------+--------+------+--------+---------------------+
| id | name | age | gender | cometime |
+------+--------+------+--------+---------------------+
| 1 | 张三 | -18 | M | 2020-07-14 19:34:04 |
+------+--------+------+--------+---------------------+
4 rows in set (0.00 sec)
5).建表数据属性
not null： #非空
primary key： #主键（唯一且非空的）
auto_increment： #自增（此列必须是：primary key或者unique key）
unique key： #唯一键，单独的唯一的
default： #默认值
unsigned： #非负数
comment： #注释
6).加上属性建表（真正的建表）
#1.创建库
mysql> create database student charset utf8;
Query OK, 1 row affected (0.00 sec)
#2.进入
mysql> use student;
Database changed
#3.创建表
mysql> create table student( id int primary key auto_increment comment "id", name varchar(12) not null comment "姓名", age tinyint unsigned not null comment "年龄", gender enum('男','女') default '男' comment "性别", cometime datetime default now() comment "入狱时间");
#3.1好看点的
mysql> create table student(
-> id int primary key auto_increment comment "id",
-> name varchar(12) not null comment "姓名",
-> age tinyint unsigned not null comment "年龄",
-> gender enum('男','女') default '男' comment "性别",
-> cometime datetime default now() comment "入狱时间");
Query OK, 0 rows affected (0.11 sec)
#4.插入数据
##要是要自己插入时间，记得在/etc/my.cnf中加上
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
##重启数据库
##前面设了默认值的，所以有些默认就好了
mysql> insert student(name,age) values('张三',28);
Query OK, 1 row affected (0.04 sec)
mysql> insert student(name,age) values('李四',39);
Query OK, 1 row affected (0.12 sec)
mysql> insert student(name,age,gender) values('王五',30,'女');
Query OK, 1 row affected (0.01 sec)
#5.查看表
```bash
mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+--------+-----+--------+---------------------+
| 1 | 张三 | 28 | 男 | 2020-07-14 19:58:36 |
| 2 | 李四 | 39 | 男 | 2020-07-14 19:58:43 |
| 3 | 王五 | 30 | 女 | 2020-07-14 20:07:33 |
+----+--------+-----+--------+---------------------+
3 rows in set (0.00 sec)
3.drop对库和对表,delete删除表中数据
##1.删库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
##2.删除表中数据
mysql> delete from student where id=3;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+--------+-----+--------+---------------------+
| 1 | 张三 | 28 | 男 | 2020-07-14 19:58:36 |
| 2 | 李四 | 39 | 男 | 2020-07-14 19:58:43 |
+----+--------+-----+--------+---------------------+
2 rows in set (0.00 sec)
##3.删表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
ERROR 1146 (42S02): Table 'student.student' doesn't exist
四.DCL:数据控制语言（grant，revoke）
1.grant授权
#1.授权语句
grant all on *.* to root@'172.16.1.%' identified by '123';
#2.查看用户权限
mysql> show grants for root@'localhost';
#3.扩展参数
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_conncetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_conncetions：允许同时连接数量
#例：每小时可发出两次查询，允许同时连接两个
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_user_connections 2;
Query OK, 0 rows affected (0.00 sec)
2.revoke回收权限
#1.回收drop权限
mysql> revoke drop on *.* from root@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
#2.查看该用户的权限（没有drop权限了）
mysql> show grants for lhd@'172.16.1.%';
#3.所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DROP, GRANT
3.授权超级管理员
grant all on *.* to root@'172.16.1.%' identified by '123' with grant option;
#授权时with grant option 加上自己，就是全部权限了
