mysql增删改查
2021/4/30 2:26:52
本文主要是介绍mysql增删改查,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
mysql
mysql显示支持的字符集不进入某数据库而列出其包含的所有表查看表结构查询表的创建命令查看数据库的创建命令查看某表的状态查看帮助增插入插入多条数据改修改数据更新多条数据查只显示特定的字段别名只显示符合条件的字段显示不符合条件的字段查看数据为空的字段查看所有数据不为空的字段显示满足多个条件的字段升序排列数据降序排列数据将特定的数据以升序或降序排列删删除数据删除整张表truncate(不可还原删除)查看授权授权用户在数据库本机上登录访问所有数据库查看指定用户的授权移除授权
显示支持的字符集
mysql> SHOW CHARACTER SET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ rows in set (0.00 sec)
不进入某数据库而列出其包含的所有表
mysql> SHOW TABLES FROM bus; Empty set (0.00 sec)
查看表结构
mysql> DESC bus.man; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ rows in set (0.11 sec)
查询表的创建命令
mysql> SHOW CREATE TABLE bus.man; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | man | CREATE TABLE `man` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `age` tinyint(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec)
查看数据库的创建命令
mysql> show create database bus; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | bus | CREATE DATABASE `bus` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ row in set (0.00 sec)
查看某表的状态
mysql> SHOW TABLE STATUS LIKE 'man'\G *************************** 1. row *************************** Name: man Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-04-29 15:07:15 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: row in set (0.00 sec) mysql> CREATE TABLE man1 (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE man2 (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLE STATUS LIKE 'man_'\G *************************** 1. row *************************** Name: man1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-04-29 15:16:30 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *************************** 2. row *************************** Name: man2 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-04-29 15:16:37 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: rows in set (0.00 sec)
\G:分行显示
查看帮助
mysql> help create table;
hrlp create [参数]
mysql> help create databases; Nothing found Please try to run 'help contents' for a list of all accessible topics 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 CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: http://dev.mysql.com/doc/refman/5.7/en/create-database.html
增
插入
mysql> desc man; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> INSERT INTO man (id,name,age) VALUE (1,'jack',27); Query OK, 1 row affected (0.01 sec) mysql> desc man -> ; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> select * from man; +----+------+------+ | id | name | age | +----+------+------+ | 1 | jack | 27 | | 1 | jack | 27 | +----+------+------+ rows in set (0.00 sec)
没设置主键可以相同id
插入多条数据
mysql> insert man values(4,'a',1),(5,'b',2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from man; +----+------+------+ | id | name | age | +----+------+------+ | 1 | jack | 27 | | 1 | jack | 27 | | 4 | a | 1 | | 5 | b | 2 | +----+------+------+ rows in set (0.00 sec)
改
修改数据
mysql> select * from man; +----+------+------+ | id | name | age | +----+------+------+ | 1 | jack | 27 | | 1 | jack | 27 | | 4 | a | 1 | | 5 | b | 2 | +----+------+------+ rows in set (0.00 sec) mysql> update man set id = 2 where name = 'jack'; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from man; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 1 | | 5 | b | 2 | +----+------+------+ rows in set (0.00 sec)
更新多条数据
mysql> update man set age =30 where id =5 or id = 4; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from man; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | +----+------+------+ rows in set (0.00 sec)
查
只显示特定的字段
mysql> select age from man; +------+ | age | +------+ | 27 | | 27 | | 30 | | 30 | +------+ rows in set (0.00 sec)
别名
mysql> select name as na,age as ag from man; +------+------+ | na | ag | +------+------+ | jack | 27 | | jack | 27 | | a | 30 | | b | 30 | +------+------+ rows in set (0.00 sec)
只显示符合条件的字段
mysql> select * from man where age <50; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | +----+------+------+ rows in set (0.00 sec) mysql> select * from man where age between 27 and 31; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | +----+------+------+ rows in set (0.00 sec) mysql> select * from man where name like '%a%'; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | +----+------+------+ rows in set (0.00 sec)
显示不符合条件的字段
mysql> select * from man where age !=50; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | +----+------+------+ rows in set (0.00 sec)
查看数据为空的字段
mysql> insert man values(4,'d',1),(5,'e',null); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from man -> ; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | | 4 | d | 1 | | 5 | e | 50 | | 4 | d | 1 | | 5 | e | NULL | +----+------+------+ rows in set (0.00 sec) mysql> select * from man where age is null; +----+------+------+ | id | name | age | +----+------+------+ | 5 | e | NULL | +----+------+------+ row in set (0.00 sec)
查看所有数据不为空的字段
mysql> select * from man where age is not null; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | | 4 | d | 1 | | 5 | e | 50 | | 4 | d | 1 | +----+------+------+ rows in set (0.00 sec)
显示满足多个条件的字段
mysql> select * from man where name = 'jack' and age =27; +----+------+------+ | id | name | age | +----+------+------+ | 2 | jack | 27 | | 2 | jack | 27 | +----+------+------+ rows in set (0.00 sec)
升序排列数据
mysql> select * from man order by age; +----+------+------+ | id | name | age | +----+------+------+ | 5 | e | NULL | | 4 | d | 1 | | 4 | d | 1 | | 2 | jack | 27 | | 2 | jack | 27 | | 4 | a | 30 | | 5 | b | 30 | | 5 | e | 50 | +----+------+------+ rows in set (0.00 sec)
降序排列数据
mysql> select * from man order by age desc; +----+------+------+ | id | name | age | +----+------+------+ | 5 | e | 50 | | 4 | a | 30 | | 5 | b | 30 | | 2 | jack | 27 | | 2 | jack | 27 | | 4 | d | 1 | | 4 | d | 1 | | 5 | e | NULL | +----+------+------+ rows in set (0.00 sec)
将特定的数据以升序或降序排列
mysql> select * from man order by age desc limit 3; +----+------+------+ | id | name | age | +----+------+------+ | 5 | e | 50 | | 4 | a | 30 | | 5 | b | 30 | +----+------+------+ rows in set (0.00 sec) mysql> select * from man order by age limit 3; +----+------+------+ | id | name | age | +----+------+------+ | 5 | e | NULL | | 4 | d | 1 | | 4 | d | 1 | +----+------+------+ rows in set (0.00 sec)
删
删除数据
mysql> delete from man where id = 2; Query OK, 2 rows affected (0.01 sec) mysql> select * from man; +----+------+------+ | id | name | age | +----+------+------+ | 4 | a | 30 | | 5 | b | 30 | | 4 | d | 1 | | 5 | e | 50 | | 4 | d | 1 | | 5 | e | NULL | +----+------+------+ rows in set (0.00 sec)
删除整张表
mysql> delete from man; Query OK, 6 rows affected (0.00 sec) mysql> select * from man; Empty set (0.00 sec)
truncate(不可还原删除)
mysql> truncate man; Query OK, 0 rows affected (0.00 sec)
查看授权
mysql> SHOW GRANTS; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ rows in set (0.00 sec)
授权用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'hzy'@'192.168.207.131' IDENTIFIED BY 'hzy123'; Query OK, 0 rows affected, 1 warning (0.00 sec)
查看指定用户的授权
mysql> SHOW GRANTS FOR root; +-------------------------------------------+ | Grants for root@% | +-------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' | +-------------------------------------------+ row in set (0.00 sec)
移除授权
mysql> REVOKE ALL ON *.* FROM 'root'@'%'; Query OK, 0 rows affected (0.00 sec)
这篇关于mysql增删改查的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享