mysql数据库管理

最新推荐文章于 2024-08-17 14:00:00 发布

树峰

最新推荐文章于 2024-08-17 14:00:00 发布

阅读量180

点赞数

分类专栏： mysql数据库文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_50345059/article/details/111603716

版权

mysql数据库专栏收录该内容

8 篇文章

订阅专栏

mysql数据库管理

SQL语句概述.
# char和varchar的区别
mysql数据库常用语句分类

SQL语句概述.

1、SQL语言
Structured Query Language的缩写，即结构化查询语言
关系型数据库的标准语言
用于维护管理数据库
包括数据查询、数据更新、访问控制、对象管理等功能
2、SQL分类
DDL:数据定义语言
DML:数据操纵语言
DQL:数据查询语言
DCL:数据控制语言

# char和varchar的区别

1、char的长度是不可变的，而varchar的长度是可变的
字段b:类型char(10)，值为: abc，存储为: abc (abc+7个空格)
字段d:类型varchar(10)，值为: abc，存储为: abc (自动变为3个的长度)
2、超出长度自动截取
字段c:类型char(3)，值为: abcdefg，存储为: abc (defg自动删除）
字段e:类型varchar(3)，值为: abcdefg，存储为:abc (defg自动删除）
3、varchar(10)和char(10)，都表示可存10个字符，无论存放的是数字、字母还是UTF8汉字(每个汉字3字节），都可以存放10个
4、char最多可以存放255个字符
varchar的最大长度为65535个字节，varchar可存放的字符数跟编码有关字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766个字符字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845个字符

注:
一个汉字占多少长度与编码有关:
UTF-8:一个汉字=3个字节
GBK:一个汉字=2个字节

mysql数据库常用语句分类

1、DDL(Data Definition Language，数据定义语言)∶用来建立数据库、数据库对象和定义字段，如 CREATE、ALTER、DROP。
2、DML（Data Manipulation Language，数据操纵语言)∶用来插入、删除和修改数据库中的数据，如 INSERT、UPDATE、DELETE。
3、DQL (Data Query Language，数据查询语言):用来查询数据库中的数据，如SELECT。
4、DCL (Data Control Language，数据控制语言):用来控制数据库组件的存取许可、存取权限等，如 COMMIT、ROLLBACK、GRANT、REVOKE。

一、DDL

1．创建新的数据库
实例：

mysql> create database auth;//创建auth数据库
Query OK, 1 row affected (0.00 sec)

2.创建新的表
CREATE TABLE表名(字段1名称类型,字段2名称类型，…，PRIMARY KEY (主键名))

实例：
创建一个表里面添加属性

mysql> use auth;//进入auth数据库
Database changed
mysql> create table users(user_name char(64) not null primary key, user_passwd varchar(64) default '');
Query OK, 0 rows affected (0.01 sec)

mysql> describe users;    //查看user表
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_name   | char(64)    | NO   | PRI | NULL    |       |
| user_passwd | varchar(64) | YES  |     |         |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> 

mysql> create database aaa;  //创建aaa数据库
Query OK, 1 row affected (0.01 sec)

mysql> use aaa;  //进入表
Database changed
mysql> create table cj(id int(10) auto_increment primary key, age int(3) not null, name varchar(128) not null, score decimal(5));
Query OK, 0 rows affected (0.00 sec)

mysql> describe cj;  //查看表
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| age   | int(3)       | NO   |     | NULL    |                |
| name  | varchar(128) | NO   |     | NULL    |                |
| score | decimal(5,0) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3、增加一个属性修改表结构

mysql> alter table cj add column addr int(3);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cj;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| age   | int(3)       | NO   |     | NULL    |                |
| name  | varchar(128) | NO   |     | NULL    |                |
| score | decimal(5,0) | YES  |     | NULL    |                |
| addr  | int(3)       | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

4．删除一个数据表

mysql> create table cj2 like cj;   //克隆cj表的数据  并且生成cj2
Query OK, 0 rows affected (0.00 sec)

mysql> drop table cj2;  //删除cj2表
Query OK, 0 rows affected (0.01 sec)

5、删除一个数据库

mysql> create database abc;   //创建abc数据库
Query OK, 1 row affected (0.00 sec)

mysql> drop database abc;   //删除abc数据库
Query OK, 0 rows affected (0.01 sec)

二、DML

1．插入数据记录
INSERT INTO 表名(字段1，字段:2,…) VALUES(字段1的值,
字段2的值,…)

mysql> desc users;   //查看表结构
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_name   | char(64)    | NO   | PRI | NULL    |       |
| user_passwd | varchar(64) | YES  |     |         |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));  //创建zhangsan  密码123456（密文）
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into users values('lisi','abc123');   //创建lisi  user_passwd数值是abc123（不是密码，是数值）
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;  //查看users表里面的所有内容
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| lisi      | abc123                                    |
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> 


mysql> insert into cj(age,name,score,addr) values(17,'zhangsan',60,101), (19,'lisi',89,102), (19,'wangwu',88,103), (20,'wangerma',99,104), (22,'song',77,105);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from cj;
+----+-----+----------+-------+------+
| id | age | name     | score | addr |
+----+-----+----------+-------+------+
|  1 |  17 | zhangsan |    60 |  101 |
|  2 |  19 | lisi     |    89 |  102 |
|  3 |  19 | wangwu   |    88 |  103 |
|  4 |  20 | wangerma |    99 |  104 |
|  5 |  22 | song     |    77 |  105 |
+----+-----+----------+-------+------+
5 rows in set (0.00 sec)

mysql>

查询核心库

mysql> show databases;   
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

2、修改数据记录

mysql> select * from cj;  
+----+-----+----------+-------+------+
| id | age | name     | score | addr |
+----+-----+----------+-------+------+
|  1 |  17 | zhangsan |    60 |  101 |
|  2 |  19 | lisi     |    89 |  102 |
|  3 |  19 | wangwu   |    88 |  103 |
|  4 |  20 | wangerma |    99 |  104 |
|  5 |  22 | song     |    77 |  105 |
+----+-----+----------+-------+------+
5 rows in set (0.00 sec)

mysql> update cj set age=99 where name='zhangsan';   //更新zhangsan的年龄99
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from cj;
+----+-----+----------+-------+------+
| id | age | name     | score | addr |
+----+-----+----------+-------+------+
|  1 |  99 | zhangsan |    60 |  101 |
|  2 |  19 | lisi     |    89 |  102 |
|  3 |  19 | wangwu   |    88 |  103 |
|  4 |  20 | wangerma |    99 |  104 |
|  5 |  22 | song     |    77 |  105 |
+----+-----+----------+-------+------+
5 rows in set (0.00 sec)

mysql>

3、删除数据库记录

mysql> delete from cj where name='zhangsan';  //删除name是zhangsan
Query OK, 1 row affected (0.00 sec)

mysql> select * from cj;
+----+-----+----------+-------+------+
| id | age | name     | score | addr |
+----+-----+----------+-------+------+
|  2 |  19 | lisi     |    89 |  102 |
|  3 |  19 | wangwu   |    88 |  103 |
|  4 |  20 | wangerma |    99 |  104 |
|  5 |  22 | song     |    77 |  105 |
+----+-----+----------+-------+------+
4 rows in set (0.00 sec)

mysql>

三、DQL

1、查询数据记录
SELECT字段名1,字段名2,… FROM 表名WHERE 条件表达式

mysql> select * from users;
ERROR 1146 (42S02): Table 'aaa.users' doesn't exist
mysql> use auth;
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> select * from users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| lisi      | abc123                                    |
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user_name from users;  //从user表中查看user_name
+-----------+
| user_name |
+-----------+
| lisi      |
| zhangsan  |
+-----------+
2 rows in set (0.00 sec)

mysql> 


mysql> select user_name,user_passwd from users;   //查询俩个用，号隔开
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| lisi      | abc123                                    |
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

四、DCL

1.授予权限
GRANT权限列表ON数据库名.表名 TO用户名@来源地址〔 IDENTIFIED BY‘密码’]

mysql> grant select on *.* to 'lisi'@'localhost' identified by '123456';   
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for 'lisi'@'localhost';
+-------------------------------------------+
| Grants for lisi@localhost                 |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'lisi'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

[root@server1 ~]# mysql -ulisi -p123456
mysql: [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 7
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, 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 grants for 'lisi'@'localhost';
+-------------------------------------------+
| Grants for lisi@localhost                 |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'lisi'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

临时表

在数据库里临时建立的表，记录当下的短暂数据，退出数据库后，表将消失

mysql> use aaa;  //进入aaa数据库
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> create temporary table song (id int(3) not null,name varchar(64) not null);  //创建临时表
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;  //查看表  没有song表的存在
+---------------+
| Tables_in_aaa |
+---------------+
| cj            |
+---------------+
1 row in set (0.00 sec)

mysql> insert into song values (1,'shu'),(2,'feng');   //给song表添加属性
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from song;   //能临时看到表中的数据
+----+------+
| id | name |
+----+------+
|  1 | shu  |
|  2 | feng |
+----+------+
2 rows in set (0.00 sec)

mysql>

克隆表

mysql> create table cj2 like cj;;  //从cj表拷贝结构给cj2
Query OK, 0 rows affected (0.01 sec)


mysql> show tables; 
+---------------+
| Tables_in_aaa |
+---------------+
| cj            |
| cj2           |
+---------------+
2 rows in set (0.00 sec)


mysql> insert into cj2 select * from cj;   //拷贝cj表上的内容给cj2
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from cj2;   //查看cj2的内容
+----+-----+----------+-------+------+
| id | age | name     | score | addr |
+----+-----+----------+-------+------+
|  2 |  19 | lisi     |    89 |  102 |
|  3 |  19 | wangwu   |    88 |  103 |
|  4 |  20 | wangerma |    99 |  104 |
|  5 |  22 | song     |    77 |  105 |
+----+-----+----------+-------+------+
4 rows in set (0.00 sec)

mysql>