Mysql数据库基本操作
1.DDL（create，alter，drop） 创建删除数据库
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql3306.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.49 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> mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.02 sec) mysql> CREATE DATABASE IF NOT EXISTS chen DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | chen | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database chen; Query OK, 0 rows affected (0.15 sec) 查看数据库创建语句： mysql> show CREATE DATABASE chen; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | chen | CREATE DATABASE `chen` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
2.DQL查询语言
进入一个数据库：
mysql> use mysql; 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 database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
查看数据库下有那些表：
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
查看数据表的结构：
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
查询表：
mysql> select user,host from user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
7 rows in set (0.00 sec)
3、DML数据操作（insert，update，delete）
删除表中的数据：
mysql> delete from chen.test where id=20; Query OK, 1 row affected (0.03 sec)
4、DCL授权控制（grant，revoke）
创建用户：
mysql> create user suosuo@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
查看用户的授权信息：
mysql> show grants for suosuo;
+-------------------------------------------------------------------------------------------------------+
| Grants for suosuo@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'suosuo'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--USAGE 只能够登陆mysql数据库；
其他机器登陆该数据库：
[root@localhost ~]# mysql -usuosuo -p -h192.168.1.193
创建数据库报错：
mysql> create database cc;
ERROR 1044 (42000): Access denied for user 'suosuo'@'%' to database 'cc'
查询表报错：
mysql> select * from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'suosuo'@'192.168.1.192' for table 'user'
授权查询表：
mysql> grant select on mysql.user to suosuo;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
测试可以查询：
mysql> select User from mysql.user;
+--------+
| User |
+--------+
| root |
| suosuo |
| root |
| root |
| |
| root |
| |
| root |
+--------+
8 rows in set (0.00 sec)
5.查看帮助信息，查看DDL命令
mysql> ? Data Definition You asked for help about help category: "Data Definition" For more information, type 'help <item>', where <item> is one of the following topics: ALTER DATABASE ALTER EVENT ALTER FUNCTION ALTER LOGFILE GROUP ALTER PROCEDURE ALTER SCHEMA ALTER SERVER
mysql> ? create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: {
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
}
使用 ？ 命令； 来查看命令的具体用法：
mysql> ? use; Name: 'USE' Description: Syntax: USE db_name The USE statement tells MySQL to use the named database as the default (current) database for subsequent statements. This statement requires some privilege for the database or some object within it. The named database remains the default until the end of the session or another USE statement is issued: USE db1; SELECT COUNT(*) FROM mytable; # selects from db1.mytable USE db2; SELECT COUNT(*) FROM mytable; # selects from db2.mytable
6、查看DCL命令
mysql> ? Account management; You asked for help about help category: "Account Management" For more information, type 'help <item>', where <item> is one of the following topics: ALTER USER CREATE USER DROP USER GRANT RENAME USER REVOKE SET PASSWORD
7、查看DML信息
mysql> ? Data manipulation; You asked for help about help category: "Data Manipulation" For more information, type 'help <item>', where <item> is one of the following topics: CALL DELETE DO DUAL HANDLER INSERT INSERT DELAYED INSERT SELECT JOIN LOAD DATA LOAD XML REPLACE SELECT UNION UPDATE
8、创建数据库
mysql> create database if not exists suosuo charset utf8 collate utf8_general_ci; Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | 存储mysql的内置信息
| cc |
| chen |
| mysql | 存储mysql用户信息，授权等
| performance_schema | 存储mysql性能相关的数据
| suosuo |
| test |
+--------------------+
7 rows in set (0.01 sec)
查看创建语句：
mysql> show create database suosuo;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| suosuo | CREATE DATABASE `suosuo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.01 sec)
9查看默认的字符集
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 | /usr/local/mysql-5.6.49-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+ 8 rows in set (0.00 sec)
10.更改表名称
mysql> show tables; +---------------+ | Tables_in_rou | +---------------+ | uu | +---------------+ 1 row in set (0.00 sec) mysql> rename tables uu to kang; Query OK, 0 rows affected (0.05 sec)
mysql> alter table kang rename to KANG;
Query OK, 0 rows affected (0.01 sec)
查看表的信息：
mysql> desc KANG;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(90) | YES | | NULL | |
| iphone | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
查看创建表的语句
mysql> show create table KANG; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | KANG | CREATE TABLE `KANG` ( `id` int(11) DEFAULT NULL, `name` varchar(90) DEFAULT NULL, `iphone` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
11.添加字段
mysql> alter table KANG add address varchar(200) not null; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc KANG; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(90) | YES | | NULL | | | iphone | int(11) | YES | | NULL | | | address | varchar(200) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
mysql> alter table KANG add gender ENUM('male','female') not null default 'male';
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
插入数据到指点的列后面
mysql> desc KANG;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(90) | YES | | NULL | |
| iphone | int(11) | YES | | NULL | |
| gender | enum('male','female') | NO | | male | |
+--------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table KANG add address varchar(100) after name;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc KANG;
+---------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(90) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| iphone | int(11) | YES | | NULL | |
| gender | enum('male','female') | NO | | male | |
+---------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
一次插入多列：
mysql> alter table KANG add pic varchar(200),add age int after name;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
12.删除表的字段
mysql> alter table KANG drop address; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
13.修改表的数据类型：
mysql> desc KANG; +---------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(90) | YES | | NULL | | | age | int(11) | YES | | NULL | | | address | varchar(100) | YES | | NULL | | | iphone | int(11) | YES | | NULL | | | gender | enum('male','female') | NO | | male | | | pic | varchar(200) | YES | | NULL | | +---------+-----------------------+------+-----+---------+-------+ 7 rows in set (0.02 sec) mysql> alter table KANG change pic pic char(20); Query OK, 1 row affected (0.17 sec) Records: 1 Duplicates: 0 Warnings: 0
14.查看索引
mysql> show index from mysql.user\G; *************************** 1. row *************************** Table: user Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: Host Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:
添加索引：
mysql> alter table KANG add index id_index(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加主键索引：
mysql> alter table KANG add primary key (id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除索引：
mysql> alter table KANG drop index id_index;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
15.数据删除表
删除数据表：
mysql> drop table KANG; Query OK, 0 rows affected (0.01 sec)
16.delete删除数据
delete对于有自增列的数据，删除数据后，回保留其id。
mysql> select * from KNAG2; +----+--------+------+---------+--------+--------+------+ | id | name | age | address | iphone | gender | pic | +----+--------+------+---------+--------+--------+------+ | 12 | kaka | NULL | NULL | 123456 | male | NULL | | 13 | suosuo | 12 | cheng | 11111 | male | 22 | +----+--------+------+---------+--------+--------+------+ 2 rows in set (0.00 sec) mysql> delete from KNAG2 where id=12; Query OK, 1 row affected (0.01 sec)
把表格的数据都删除了：
mysql> delete from KANG;
Query OK, 7 rows affected (0.01 sec)
17.修改表的其他属性
mysql> alter table KANG modify id int(11) auto_increment; Query OK, 2 rows affected (0.22 sec) Records: 2 Duplicates: 0 Warnings: 0
18.truncate删除数据
清空数据表，自增列重新计算。表的其他配置不变。 mysql> truncate table KANG; Query OK, 0 rows affected (0.11 sec) mysql> select * from KANG; Empty set (0.00 sec) mysql> desc KANG; +---------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(90) | YES | | NULL | | | age | int(11) | YES | | NULL | | | address | varchar(100) | YES | | NULL | | | iphone | int(11) | YES | | NULL | | | gender | enum('male','female') | NO | | male | | | pic | char(20) | YES | | NULL | | +---------+-----------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
19.修改表数据update
mysql> update KANG set name='nihao' where id=8; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
