MySQL实战1
王者荣耀和SQL的故事
先看DDL和DCL
DDL数据定义，创建修改相关 1.创建
DCL权限控制， 2权限控制
英雄数据库
分类---
战士
法师
辅助
刺客
射手
数据库的创建，kings数据库
名字语法是，不得用数字开头，大小写不敏感（a和A弋阳都可以处理）
内置的关键字，默认是大写，你小写也行
#严谨的写法
#偷懒的方法不严谨，create database kings;但是比如默认的字符集就ing该提前卸载my.cnf中
create database if not exists kings default charset utf8 collate utf8_general_ci;
mysql> create database if not exists kings default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | #存储mysql的内置信息
| kings | #自己写的
| lol | #自己写的
| lol2 | #自己写的
| mysql | #存储mysql用户信息，授权
| performance_schema | #存储mysql性能相关的信息
+--------------------+
6 rows in set (0.00 sec)
#查看kings的创建信息
mysql> show create database kings;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| kings | CREATE DATABASE `kings` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
#对结构格式化，以列表展示\G
mysql> show create database kings\G
*************************** 1. row ***************************
Database: kings
Create Database: CREATE DATABASE `kings` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql字符集的学习
读写mysql的中文数据，得保证
- 客户端的编码，mysql的链接客户端，如mysql的命令，如navicat的编码，如xshell的编码
- 服务端的编码，linux服务器本身的编码，mysqld服务端的编码
- 统一
查询当前数据库的字符集信息
字符集的作用就是，计算机是如何处理，以及展示全世界各种语言的一种编码
#查看包含了类似于char的变量
show variables like 'char%';
mysql> show variables like 'char%';
+--------------------------+------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql-5.6.50-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+------------------------------------------------------------------+
8 rows in set (0.00 sec)
#字符集是不统一的，插入中文就会乱码，因此utf8字符集需要统一
#字符集章节讲解
创建英雄数据表
mysql> show databases like "k%";
+---------------+
| Database (k%) |
+---------------+
| kings |
+---------------+
1 row in set (0.00 sec)
mysql>
#查看英雄数据表
mysql> show tables from kings;
Empty set (0.00 sec)
#数据表的名称
Tanks 坦克
Warrior 战士
Assassin 刺客
Mage 法师
Archer 射手
Assist 辅助
#创建数据表的命令帮助
mysql> ? create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
#创建表
create table tb_name(字段1 字段数据类型 ，字段2 字段数据类型，字段3 字段数据类型 ) 数据表的额外参数 ;
#创建表的演练
create table lol.t1(id int);
mysql> create table lol.t1(id int);
Query OK, 0 rows affected (0.03 sec)
#
mysql> select * from lol.t1;
Empty set (0.00 sec)
mysql> select * from lol.T1;
ERROR 1146 (42S02): Table 'lol.T1' doesn't exist
#完整的创建坦克表
#注意：mysql默认关键字对大小写不敏感，但是数据表对大小写敏感，如上面查询；
#字段名字，字段数据类型 [额外的选项，可选]
# not null不为空
#engine=innodb引擎后面学习，理解就是，汽车有不同的发动机，每种发动机都有他的优缺点
#先进入kings数据库
use kings;
create table if not exists tanks(
id int,
name varchar(100) not null,
skills varchar(255) not null,
price int not null,
primary key (id)
)engine=innodb default charset utf8;
#查看有哪些数据表
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks |
+-----------------+
1 row in set (0.00 sec)
#查看数据表创建的信息
show create table tanks;
mysql> show create table tanks;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tanks | CREATE TABLE `tanks` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) NOT NULL,
`skills` varchar(255) NOT NULL,
`price` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#查看数据表的结构和数据
mysql> desc tanks;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#查看数据内容
mysql> select * from tanks;
Empty set (0.00 sec)
#插入表数据
insert into tanks(id,name,skills,price) values(1,'程咬金','大招回血，血量越低，伤害越高',8888),(2,'大鱼庄周','免疫所有队友的负面控制',2888);
mysql> insert into tanks(id,name,skills,price) values(1,'程咬金','大招回血，血量越低，伤害越高',8888),(2,'大鱼庄周','免疫所有队友的负面控制',2888);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
#查看数据表
mysql> select * from tanks;
+----+--------------+--------------------------------------------+-------+
| id | name | skills | price |
+----+--------------+--------------------------------------------+-------+
| 1 | 程咬金 | 大招回血，血量越低，伤害越高 | 8888 |
| 2 | 大鱼庄周 | 免疫所有队友的负面控制 | 2888 |
+----+--------------+--------------------------------------------+-------+
2 rows in set (0.00 sec)
mysql>
DDL之删除管理
drop语句
mysql> ? drop
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
ALTER TABLE
ALTER TABLESPACE
DEALLOCATE PREPARE
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP FUNCTION UDF
DROP INDEX
DROP PREPARE
DROP PROCEDURE
DROP SCHEMA
DROP SERVER
DROP TABLE
DROP TABLESPACE
#删除无用的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kings |
| lol |
| lol2 |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database if exists lol;
Query OK, 1 row affected (0.01 sec)
mysql> drop database if exists lol2;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database if exists lol3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop database lol3;
ERROR 1008 (HY000): Can't drop database 'lol3'; database doesn't exist
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kings |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
#删除数据表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kings |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use kings;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks |
+-----------------+
1 row in set (0.00 sec)
mysql> drop table tanks;
Query OK, 0 rows affected (0.00 sec)
DCL用户管理实战
Date Control Language
用户管理SQL的学习
#创建用户的语法
create user '用户名'@'用户登入的网段' identified by '允许该用户登入的密码';
#创建普通用户
#用户登入mysql -uroot -p123456 -h127.0.0.1
mysql> create user 'zhengzheng'@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> select user,host,password from mysql.user;
+---------------+-----------------------+-------------------------------------------+
| user | host | password |
+---------------+-----------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| zhengzheng | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhengxinqiang | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)
#可以看下他的权限
mysql> show grants for zhengzheng@'127.0.0.1';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for zhengzheng@127.0.0.1 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhengzheng'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
grant添加权限
#语法
grant 允许执行的权限1，权限2，权限3 on 数据库.数据表 to 用户@'用户允许主机' identified by '密码';
#允许zhengzheng用户访问tanks表
mysql> grant select on kings.tanks to zhengzheng@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for zhengzheng@'127.0.0.1';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for zhengzheng@127.0.0.1 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhengzheng'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `kings`.`tanks` TO 'zhengzheng'@'127.0.0.1' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
用zhengzheng用户
[root@localhost ~]# mysql -uzhengzheng -h 127.0.0.1 -P3306 -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.50-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kings |
+--------------------+
2 rows in set (0.00 sec)
mysql> use kings;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from tanks;
+----+--------------+--------------------------------------------+-------+
| id | name | skills | price |
+----+--------------+--------------------------------------------+-------+
| 1 | 程咬金 | 大招回血，血量越低，伤害越高 | 8888 |
| 2 | 大鱼庄周 | 免疫所有队友的负面控制 | 2888 |
+----+--------------+--------------------------------------------+-------+
2 rows in set (0.00 sec)
注意：如果用grant权限的identified by '如果这个改了密码就只能用这个登入'
zhengzheng该账号不能远程登入，只能本地通过127.0.0.1登入
浙公网安备 33010602011771号