03.sql语句-创建+删除+修改+更新+增加+查询

MYSQL语句

查看数据库

show databases;

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

3 rows in set (0.01 sec)

数据库解释

information_schema这数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型不访问权限等。 [ˈskimə]

performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA [pəˈfɔ:məns] 主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表

mysql库是系统库，里面保存有账户信息，权限信息等。

mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。

show databases \G;

MariaDB [(none)]> show databases \G;

*************************** 1. row ***************************

Database: information_schema

*************************** 2. row ***************************

Database: mysql

*************************** 3. row ***************************

Database: performance_schema

3 rows in set (0.00 sec)

ERROR: No query specified

mysql -uroot -p123456 -e "show databases"

[root@localhost ~]# mysql -uroot -p123456 -e "show databases"

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

mysqlshow -uroot -p123456

[root@localhost ~]# mysqlshow -uroot -p123456

+--------------------+

| Databases |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

创建数据库

语法：create database 数据库名;

创建数据库注意事项

在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库。因此，上面命令中的数据库名字必须与操作系统的约束的目录名字一致。例如不允许文件和目录名中有\,/,:,*,?,”,<,>,|这些符号，在MySQL数据库名字中这些字母会被自动删除。<遵从目录的约束>

数据库的名字不能超过64个字符，包含特殊字符的名字或者是全部由数字或保留字组成的名字必须用单引号``包起来。

数据库不能重名。

create database Test;

MariaDB [(none)]> create database Test;

Query OK, 1 row affected (0.00 sec)

create database if not exists Test;

使用if not exists子语句以避免创建存在的数据库时，出现MySQL错误信息

MariaDB [(none)]> create database if not exists Test;

Query OK, 1 row affected (0.00 sec)

show databases;

查看数据库

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| Test |

| mysql |

| performance_schema |

+--------------------+

4 rows in set (0.00 sec)

ls /var/lib/mysql/

shell命令行下查看数据库存放目录，有的存放在ls /var/lib/mysql/下，有的存放在ls /usr/local/mysql/data/下。

[root@localhost ~]# ls /var/lib/mysql/

aria_log.00000001 ibdata1 ib_logfile1 mysql.sock Test

aria_log_control ib_logfile0 mysql performance_schema

使用数据库

使用USE语句将会选择一个数据库成为当前数据库。后面的操作默认都在被选择的数据库中操作。

use Test;

切换数据库

MariaDB [(none)]> use Test;

Database changed

select database();

查看自己所处的数据库及默认所在数据库

MariaDB [Test]> select database();

+------------+

| database() |

+------------+

| Test |

+------------+

1 row in set (0.00 sec)

默认NULL

MariaDB [(none)]> select database();

+------------+

| database() |

+------------+

| NULL |

+------------+

1 row in set (0.00 sec)

NULL意味着没有选择数据库

Null在数据库中表示不知道的数据，主要有3种意思：

1）知道数据存在，但不知道具体值.

2）不知道数据是否存在.

3）数据不存在.

mysql -uroot -p123456 Test

shell命令行选择数据库直接跳入该数据库

[root@localhost ~]# mysql -uroot -p123456 Test

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 25

Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [Test]>

select now(),user(),database();

查看当前时间，用户名，数据库名

MariaDB [Test]> select now(),user(),database();

+---------------------+----------------+------------+

| now() | user() | database() |

+---------------------+----------------+------------+

| 2020-12-17 11:26:55 | root@localhost | Test |

+---------------------+----------------+------------+

1 row in set (0.00 sec)

删除数据库

drop database Test;

删除时，没有任何删除提示，谨慎使用！！！（删除时，有版本需要使用引号括数据库名，有版本不允许用引号括数据库）

MariaDB [(none)]> drop database Test;

Query OK, 0 rows affected (0.00 sec)

rm -rf Test/

shell命令行吓删除数据库

[root@localhost ~]# cd /var/lib/mysql/

[root@localhost mysql]# rm -rf Test/

drop database if exists Test;

使用if exists子语句以避免删除不存在的数据库时，出现MySQL错误信息。

MariaDB [(none)]> drop database if exists Test;

Query OK, 0 rows affected, 1 warning (0.00 sec)

show warnings;

查看最近一次报错信息

MariaDB [(none)]> create database Test;

ERROR 1007 (HY000): Can't create database 'Test'; database exists

MariaDB [(none)]> show warnings;

+-------+------+-----------------------------------------------+

| Level | Code | Message |

+-------+------+-----------------------------------------------+

| Error | 1007 | Can't create database 'Test'; database exists |

+-------+------+-----------------------------------------------+

1 row in set (0.00 sec)

创建表

语法：create table 表名 (字段名类型,字段名类型,字段名类型);

MariaDB [(none)]> use Test;

Database changed

create table info(id int(5),name char(20),age int(10));

表的创建必须在某一数据库内才能创建！！！

MariaDB [Test]> create table info(id int(5),name char(20),age int(10));

Query OK, 0 rows affected (0.01 sec)

show create table info \G

查看创建表执行了哪些命令

MariaDB [Test]> show create table info \G

*************************** 1. row ***************************

Table: info

Create Table: CREATE TABLE `info` (

`id` int(5) DEFAULT NULL,

`name` char(20) DEFAULT NULL,

`age` int(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

create table user(id int(5),name char(10),age int(15),gender char(20))ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建表时，可以指定字段名、字段类型、数据库存储引擎、数据库字符集。

MariaDB [Test]> create table user(id int(5),name char(10),age int(15),gender char(20))ENGINE=MyISAM DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.00 sec)

show create table user \G

查看创建表执行了哪些命令

MariaDB [Test]> show create table user \G

*************************** 1. row ***************************

Table: user

Create Table: CREATE TABLE `user` (

`id` int(5) DEFAULT NULL,

`name` char(10) DEFAULT NULL,

`age` int(15) DEFAULT NULL,

`gender` char(20) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

查看表

show tables;

查看数据库中有哪些表。

MariaDB [Test]> show tables;

+----------------+

| Tables_in_Test |

+----------------+

| info |

+----------------+

1 row in set (0.00 sec)

desc info;

查看表的结构。

MariaDB [Test]> desc info;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

explain Test.info;

指定查询某个库中的某个表的表结构。

MariaDB [(none)]> explain Test.info;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

show columns from Test.info;

指定查询某个库中的某个表的表结构。

MariaDB [(none)]> show columns from Test.info;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

show fields from Test.info;

指定查询某个库中的某个表的表结构。

MariaDB [(none)]> show fields from Test.info;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

show columns from Test.info like 'name';

查看某一数据库中某一表内的某一字段的结构信息

MariaDB [(none)]> show columns from Test.info like 'name';

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

1 row in set (0.00 sec)

删除表

drop table user;

删除时，没有任何删除提示，谨慎使用！！！（删除时，有版本需要使用引号括数据库名，有版本不允许用引号括数据库）

MariaDB [Test]> drop table user;

Query OK, 0 rows affected (0.00 sec)

禁止预读表信息

出现如下提示，可以使用-A参数解决。

MariaDB [Test]> drop table user;

Query OK, 0 rows affected (0.00 sec)

MariaDB [Test]> use performance_schema;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with –A

[root@localhost ~]# mysql -uroot -p123456 -A

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 31

Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use performance_schema;

Database changed

alter table userinfo drop age;删除表中字段

语法：alter table 表名 drop 字段名;

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

MariaDB [Test]> alter table userinfo drop age;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(5) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改表

alter table info engine=MyISAM;修改表存储引擎

MariaDB [books]> alter table info engine=MyISAM;

Query OK, 19 rows affected (0.01 sec)

Records: 19 Duplicates: 0 Warnings: 0

MariaDB [books]> show create table info \G

*************************** 1. row ***************************

Table: info

Create Table: CREATE TABLE `info` (

`id` int(5) DEFAULT NULL,

`iname` char(20) DEFAULT NULL,

`pace` int(3) DEFAULT NULL,

`address` char(20) DEFAULT NULL,

`lid` int(5) DEFAULT NULL,

KEY `iname_index` (`iname`,`pace`),

KEY `lid_key` (`lid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

alter table info rename userinfo;修改表名

语法：alter table 表名 rename 新表名;

MariaDB [Test]> show tables;

+----------------+

| Tables_in_Test |

+----------------+

| info |

+----------------+

1 row in set (0.00 sec)

MariaDB [Test]> alter table info rename userinfo;

Query OK, 0 rows affected (0.01 sec)

MariaDB [Test]> show tables;

+----------------+

| Tables_in_Test |

+----------------+

| userinfo |

+----------------+

1 row in set (0.00 sec)

alter table userinfo modify name int(20);修改字段类型

语法：alter table 表名 modify 要修改的字段名要修改的字段类型;

MariaDB [Test]> desc userinfo;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

MariaDB [Test]> alter table userinfo modify name int(20);

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [Test]> desc userinfo;

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| name | int(20) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

3 rows in set (0.00 sec)

alter table userinfo change name new_name char(30);修改字段名和字段类型

语法：alter table 表名 change 原字段名新字段名新字段类型;

MariaDB [Test]> desc userinfo;

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| name | int(20) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

3 rows in set (0.00 sec)

MariaDB [Test]> alter table userinfo change name new_name char(30);

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [Test]> desc userinfo;

+----------+----------+------+-----+---------+-------+

+----------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

change和modify的区别

CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）

alter table userinfo add gender enum('M','W');在表中添加新字段

语法：alter table 表名 add 字段名字段类型;

MariaDB [Test]> desc userinfo;

+----------+----------+------+-----+---------+-------+

+----------+----------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

MariaDB [Test]> alter table userinfo add gender enum('M','W');

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

alter table userinfo add uid int(10) first;在表的第一列添加一个字段

语法：alter table 表名 add 新加字段名新加字段类型 first;

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

MariaDB [Test]> alter table userinfo add uid int(10) first;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

alter table userinfo add address char(50) after age;在表的某一指定字段之后添加一个字段

语法：alter table 表名 add 新加字段名新加字段类型 after 原字段名;

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

MariaDB [Test]> alter table userinfo add address char(50) after age;

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [Test]> desc userinfo;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(5) | YES | | NULL | |

| age | int(10) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

增加表中数据

insert into userinfo values(1,1.1,'wangbin','anyang','M');单条数据插入

此处使用values插入值时，需要插入值的个数需与字段个数一直，否则报“没有足够多条值插入”

语法：insert into 表名 values(字段值1,字段值2,字段值3,……);

MariaDB [Test]> insert into userinfo values(1,1.1,'wangbin','anyang','M');

Query OK, 1 row affected (0.00 sec)

insert into userinfo values(2,1.2,'changmengka','anyang','W'),(3,1.3,'liuzhiwei','anyang','M');多条数据同时插入

此处使用values插入值时，需要插入值的个数需与字段个数一直，否则报“没有足够多条值插入”

语法：insert into 表名 values(字段值1,字段值2,……),(字段值1,字段值2,……);

MariaDB [Test]> insert into userinfo values(2,1.2,'changmengka','anyang','W'),(3,1.3,'liuzhiwei','anyang','M');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

insert into userinfo (uid,id,new_name) values(4,1.4,'wangting');指定字段插入数据

语法：insert into userinfo (字段名1,字段名2,……) values(字段1值,字段2值,……);

MariaDB [Test]> insert into userinfo (uid,id,new_name) values(4,1.4,'wangting');

Query OK, 1 row affected (0.00 sec)

查询表中数据

select * from userinfo;查看表中所有数据

语法：select * from 表名;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+---------+--------+

+------+------+-------------+---------+--------+

| 1 | 1 | wangbin | anyang | M |

| 2 | 1 | changmengka | anyang | W |

| 3 | 1 | liuzhiwei | anyang | M |

+------+------+-------------+---------+--------+

4 rows in set (0.00 sec)

select * from userinfo \G查看表中所有数据

当表中字段比较多时，可食用\G转换成其他格式显示。

MariaDB [Test]> select * from userinfo \G

*************************** 1. row ***************************

uid: 1

id: 1

new_name: wangbin

address: anyang

gender: M

*************************** 2. row ***************************

uid: 2

id: 1

new_name: changmengka

address: anyang

gender: W

*************************** 3. row ***************************

uid: 3

id: 1

new_name: liuzhiwei

address: anyang

gender: M

*************************** 4. row ***************************

uid: 4

id: 1

new_name: wangting

address: NULL

gender: NULL

4 rows in set (0.00 sec)

select new_name from userinfo;只查看表中的某个字段

语法：select 字段名 from 表名;

MariaDB [Test]> select new_name from userinfo;

+-------------+

| new_name |

+-------------+

| wangbin |

| changmengka |

| liuzhiwei |

| wangting |

+-------------+

4 rows in set (0.00 sec)

select new_name,gender from userinfo;只查看表中的某些字段

语法：select 字段名1,字段名2 form 表名;

MariaDB [Test]> select new_name,gender from userinfo;

+-------------+--------+

| new_name | gender |

+-------------+--------+

| wangbin | M |

| changmengka | W |

| liuzhiwei | M |

| wangting | NULL |

+-------------+--------+

4 rows in set (0.00 sec)

跨数据库查表

在本数据库中查看别的数据库中的表。

select * from Test.userinfo;

语法：select 字段 from 数据库名.表名;

注意数据库名与表名之间的“.”

举例1：

MariaDB [(none)]> select * from Test.userinfo;

+------+------+-------------+---------+--------+

+------+------+-------------+---------+--------+

| 1 | 1 | wangbin | anyang | M |

| 2 | 1 | changmengka | anyang | W |

| 3 | 1 | liuzhiwei | anyang | M |

+------+------+-------------+---------+--------+

4 rows in set (0.00 sec)

insert into Test.userinfo(new_name,gender) values('weifangchao','W');

语法：insert into 数据库名.表名 (字段名1,字段名2,……) values(字段1值,字段2值,……);

举例2：

MariaDB [(none)]> insert into Test.userinfo(new_name,gender) values('weifangchao','W');

Query OK, 1 row affected (0.00 sec)

alter table Test.userinfo add age int(10) after new_name;

语法：alter table 数据库名.表名 add 新加字段名新加字段类型 after 原字段名;

举例：

MariaDB [(none)]> alter table Test.userinfo add age int(10) after new_name;

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

删除表中数据

如果省略where条件，将删除全部记录

delete from userinfo where Uid=3;删除表中指定数据

语法：delete from 表名 where 定位字段=定位字段值;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | W |

| 3 | 1 | liuzhiwei | NULL | anyang | M |

+------+------+-------------+------+---------+--------+

5 rows in set (0.00 sec)

MariaDB [Test]> delete from userinfo where Uid=3;

Query OK, 1 row affected (0.00 sec)

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | W |

+------+------+-------------+------+---------+--------+

4 rows in set (0.00 sec)

delete from userinfo where address is null;删除表中满足条件的数据

语法：delete from 表名 where 定位字段名 is 定位字段值;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | W |

+------+------+-------------+------+---------+--------+

4 rows in set (0.00 sec)

MariaDB [Test]> delete from userinfo where address is null;

Query OK, 2 rows affected (0.00 sec)

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | W |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

更新表中数据

如果省略WHERE条件将更新全部记录

update userinfo set gender="M" where id=2;更新表中指定指字段的值

语法：update 表名 set 要修改字段名=要修改字段值 where 定位字段名=定位字段值;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | W |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

MariaDB [Test]> update userinfo set gender="M" where uid=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | M |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

update userinfo set gender='w';将某字段的值都修改成某值

语法：update userinfo set 要修改字段名=要修改字段值;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | NULL | anyang | M |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

MariaDB [Test]> update userinfo set gender='w';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | W |

| 2 | 1 | changmengka | NULL | anyang | W |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

update userinfo set gender='M',age=26 where uid=2;修改满足条件后的多个字段

语法：update userinfo set 要修改字段名=要修改字段值,要修改字段名=要修改字段值,…… where 定位字段名=定位字段值;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | W |

| 2 | 1 | changmengka | NULL | anyang | W |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

MariaDB [Test]> update userinfo set gender='M',age=26 where uid=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | W |

| 2 | 1 | changmengka | 26 | anyang | M |

+------+------+-------------+------+---------+--------+

2 rows in set (0.00 sec)

其他select语句

select new_name,age from userinfo;查询表中某些特定字段

语法：select 字段名1,字段名2 from 表名;

MariaDB [Test]> select new_name,age from userinfo;

+-------------+------+

| new_name | age |

+-------------+------+

| wangbin | NULL |

| changmengka | 26 |

+-------------+------+

2 rows in set (0.00 sec)

select distinct new_name,gender from userinfo;去重复查询某些字段

语法：select distinct 字段名1,字段名2,…… from 表名;

MariaDB [Test]> select * from userinfo;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 1 | 1 | wangbin | NULL | anyang | M |

| 2 | 1 | changmengka | 26 | anyang | M |

+------+------+-------------+------+---------+--------+

3 rows in set (0.00 sec)

MariaDB [Test]> select distinct new_name,gender from userinfo;

+-------------+--------+

| new_name | gender |

+-------------+--------+

| wangbin | M |

| changmengka | M |

+-------------+--------+

2 rows in set (0.00 sec)

select distinct new_name,gender from userinfo where uid=2;在某条件下去重复查询某些字段

语法：select distinct 字段名1,字段名2,…… from 表名 where 定位字段名=定位字段值;

MariaDB [Test]> select distinct new_name,gender from userinfo where uid=2;

+-------------+--------+

| new_name | gender |

+-------------+--------+

| changmengka | M |

+-------------+--------+

1 row in set (0.00 sec)

select new_name,gender from userinfo where uid<3 and age>20;在某多条件同时满足情况下查看某些字段

语法：select 字段名1,字段名2,…… from 表名 where 定位字段1<定位字段值 and 定位字段2>定位字段值;

同时满足and两边条件情况下，在做查询

MariaDB [Test]> select new_name,gender from userinfo where uid<3 and age>20;

+-------------+--------+

| new_name | gender |

+-------------+--------+

| changmengka | M |

+-------------+--------+

1 row in set (0.00 sec)

select new_name,gender from userinfo where uid<3 or age>20;在满足某多条件下满足其中一个的情况下查看某些字段

语法：select 字段名1,字段名2,…… from 表名 where 定位字段名1<定位字段值 or 定位字段2>定位字段值

MariaDB [Test]> select new_name,gender from userinfo where uid<3 or age>20;

+-------------+--------+

| new_name | gender |

+-------------+--------+

| wangbin | M |

| changmengka | M |

+-------------+--------+

2 rows in set (0.00 sec)

select new_name,address from userinfo where binary address='anyang';在某条件下区分大小写查询某些字段

语法：select 字段名1,字段名2,…… from 表名 where binary 定位字段名=定位字段值;

select在默认情况下的查询不区分大小写；

BINARY是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。

MariaDB [Test]> select new_name,address from userinfo where address='anyang'; +-------------+---------+

| new_name | address |

+-------------+---------+

| wangbin | anyang |

| changmengka | ANYANG |

+-------------+---------+

2 rows in set (0.00 sec)

MariaDB [Test]> select new_name,address from userinfo where binary address='anyang';

+----------+---------+

| new_name | address |

+----------+---------+

| wangbin | anyang |

+----------+---------+

1 row in set (0.00 sec)

select distinct uid,new_name from userinfo order by id asc;在去重情况下进行升序排列并显示某些字段

语法：select distinct 字段名1,字段名2,…… from 表名 order by 定位字段 asc;

MariaDB [Test]> select distinct uid,new_name from userinfo order by id asc;

+------+-------------+

| uid | new_name |

+------+-------------+

| NULL | wangbin |

| 2 | changmengka |

| 1 | wangbin |

+------+-------------+

3 rows in set (0.00 sec)

select distinct uid,new_name from userinfo order by id desc;在去重情况下进行降序排列并显示某些字段

语法：select distinct 字段名1,字段名2,…… from 表名 order by 定位字段 desc;

MariaDB [Test]> select distinct uid,new_name from userinfo order by id desc;

+------+-------------+

| uid | new_name |

+------+-------------+

| 2 | changmengka |

| 1 | wangbin |

| NULL | wangbin |

+------+-------------+

3 rows in set (0.00 sec)

select now();打印当前的时间和日期

MariaDB [Test]> select now();

+---------------------+

| now() |

+---------------------+

| 2020-12-17 19:27:06 |

+---------------------+

1 row in set (0.00 sec)

select curdate();打印当前日期

MariaDB [Test]> select curdate();

+------------+

| curdate() |

+------------+

| 2020-12-17 |

+------------+

1 row in set (0.00 sec)

select curtime();打印当前时间

MariaDB [Test]> select curtime();

+-----------+

| curtime() |

+-----------+

| 19:29:15 |

+-----------+

1 row in set (0.00 sec)

select database();打印当前使用的数据库

MariaDB [Test]> select database();

+------------+

| database() |

+------------+

| Test |

+------------+

1 row in set (0.00 sec)

select version();打印当前MySQL版本

MariaDB [Test]> select version();

+----------------+

| version() |

+----------------+

| 5.5.60-MariaDB |

+----------------+

1 row in set (0.00 sec)

select user();打印当前用户

MariaDB [Test]> select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

其他show语句

show status;查看系统运行状态所有信息

MariaDB [Test]> show status;

+------------------------------------------+-------------+

| Variable_name | Value |

+------------------------------------------+-------------+

| Aborted_clients | 1 |

| Aborted_connects | 8 |

| Access_denied_errors | 0 |

| Aria_pagecache_blocks_not_flushed | 0 |

| Aria_pagecache_blocks_unused | 15737 |

| Aria_pagecache_blocks_used | 0 |

| Aria_pagecache_read_requests | 0 |

show global status like 'Thread%';查看系统运行状态关键字信息

MariaDB [Test]> show global status like 'Thread%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| Threadpool_idle_threads | 0 |

| Threadpool_threads | 0 |

| Threads_cached | 0 |

| Threads_connected | 2 |

show variables;查看系统所有信息

show global variables;查看系统所有信息

show global variables like ‘%version%’;查看所有系统版本信息

MariaDB [Test]> show variables like '%version%';

+-------------------------+----------------------+

| Variable_name | Value |

+-------------------------+----------------------+

| innodb_version | 5.5.59-MariaDB-38.11 |

| protocol_version | 10 |

| slave_type_conversions | |

| version | 5.5.60-MariaDB |

| version_comment | MariaDB Server |

| version_compile_machine | x86_64 |

| version_compile_os | Linux |

+-------------------------+----------------------+

7 rows in set (0.01 sec)

show variables like '%storage_engine%';查看默认存储引擎

MariaDB [Test]> show variables like '%storage_engine%';

+------------------------+--------+

| Variable_name | Value |

+------------------------+--------+

| default_storage_engine | InnoDB |

| storage_engine | InnoDB |

+------------------------+--------+

2 rows in set (0.00 sec)

show engines;查看支持哪些存储引擎

MariaDB [Test]> show engines;

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

| CSV | YES | CSV storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

10 rows in set (0.00 sec)

表的别名

select new_name as '名字',address as '地址' from userinfo;字段引用别名显示某些字段信息

语法：select 字段名1 as 字段名1的别名,字段名2 as 字段名2的别名 from 表名;

MariaDB [Test]> select new_name as '名字',address as '地址' from userinfo;

+-------------+--------+

| 名字 | 地址 |

+-------------+--------+

| wangbin | anyang |

| changmengka | ANYANG |

| wangbin | NULL |

+-------------+--------+

3 rows in set (0.00 sec)

select * from userinfo as uif where uid=2;表引用别名

等待补充详细

语法：select 字段名1,字段名2,…… from 表名 as 表的别名 where 定位字段名=定位字段值;

MariaDB [Test]> select * from userinfo as uif where uid=2;

+------+------+-------------+------+---------+--------+

| uid | id | new_name | age | address | gender |

+------+------+-------------+------+---------+--------+

| 2 | 1 | changmengka | 26 | ANYANG | M |

+------+------+-------------+------+---------+--------+

1 row in set (0.00 sec)

MySQL命令帮助

help show

MariaDB [Test]> help show;

Name: 'SHOW'

Description:

SHOW has many forms that provide information about databases, tables,

columns, or status information about the server. This section describes

those following:

SHOW AUTHORS

SHOW {BINARY | MASTER} LOGS

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

SHOW CHARACTER SET [like_or_where]

SHOW COLLATION [like_or_where]

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]

SHOW CONTRIBUTORS

SHOW CREATE DATABASE db_name

SHOW CREATE EVENT event_name

SHOW CREATE FUNCTION func_name

SHOW CREATE PROCEDURE proc_name

SHOW CREATE TABLE tbl_name

SHOW CREATE TRIGGER trigger_name

SHOW CREATE VIEW view_name

SHOW DATABASES [like_or_where]

SHOW ENGINE engine_name {STATUS | MUTEX}

SHOW [STORAGE] ENGINES

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW EVENTS

SHOW FUNCTION CODE func_name

SHOW FUNCTION STATUS [like_or_where]

SHOW GRANTS FOR user

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW MASTER STATUS

SHOW OPEN TABLES [FROM db_name] [like_or_where]

SHOW PLUGINS

SHOW PROCEDURE CODE proc_name

SHOW PROCEDURE STATUS [like_or_where]

SHOW PRIVILEGES

SHOW [FULL] PROCESSLIST

SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]

SHOW PROFILES

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

SHOW [GLOBAL | SESSION] STATUS [like_or_where]

SHOW TABLE STATUS [FROM db_name] [like_or_where]

SHOW [FULL] TABLES [FROM db_name] [like_or_where]

SHOW TRIGGERS [FROM db_name] [like_or_where]

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:

LIKE 'pattern'

| WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'

part, 'pattern' is a string that can contain the SQL "%" and "_"

wildcard characters. The pattern is useful for restricting statement

output to matching values.

Several SHOW statements also accept a WHERE clause that provides more

flexibility in specifying which rows to display. See

http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/show.html

help select

MariaDB [Test]> help select

Name: 'SELECT'

Description:

Syntax:

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr [, select_expr ...]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and

can include UNION statements and subqueries. See [HELP UNION], and

http://dev.mysql.com/doc/refman/5.5/en/subqueries.html.

The most commonly used clauses of SELECT statements are these:

o Each select_expr indicates a column that you want to retrieve. There

must be at least one select_expr.

o table_references indicates the table or tables from which to retrieve

rows. Its syntax is described in [HELP JOIN].

o The WHERE clause, if given, indicates the condition or conditions

that rows must satisfy to be selected. where_condition is an

expression that evaluates to true for each row to be selected. The

statement selects all rows if there is no WHERE clause.

In the WHERE expression, you can use any of the functions and

operators that MySQL supports, except for aggregate (summary)

functions. See

http://dev.mysql.com/doc/refman/5.5/en/expressions.html, and

http://dev.mysql.com/doc/refman/5.5/en/functions.html.

SELECT can also be used to retrieve rows computed without reference to

any table.

URL: http://dev.mysql.com/doc/refman/5.5/en/select.html

help alter

MariaDB [Test]> help alter;

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 DATABASE

ALTER EVENT

ALTER FUNCTION

ALTER LOGFILE GROUP

ALTER PROCEDURE

ALTER SERVER

ALTER TABLE

ALTER TABLESPACE

ALTER VIEW

GRANT

SPATIAL

help update

MariaDB [Test]> help update;

Name: 'UPDATE'

Description:

Syntax:

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

For the single-table syntax, the UPDATE statement updates columns of

existing rows in the named table with new values. The SET clause

indicates which columns to modify and the values they should be given.

Each value can be given as an expression, or the keyword DEFAULT to set

a column explicitly to its default value. The WHERE clause, if given,

specifies the conditions that identify which rows to update. With no

WHERE clause, all rows are updated. If the ORDER BY clause is

specified, the rows are updated in the order that is specified. The

LIMIT clause places a limit on the number of rows that can be updated.

For the multiple-table syntax, UPDATE updates rows in each table named

in table_references that satisfy the conditions. In this case, ORDER BY

and LIMIT cannot be used.

where_condition is an expression that evaluates to true for each row to

be updated. For expression syntax, see

http://dev.mysql.com/doc/refman/5.5/en/expressions.html.

table_references and where_condition are is specified as described in

http://dev.mysql.com/doc/refman/5.5/en/select.html.

You need the UPDATE privilege only for columns referenced in an UPDATE

that are actually updated. You need only the SELECT privilege for any

columns that are read but not modified.

The UPDATE statement supports the following modifiers:

o With the LOW_PRIORITY keyword, execution of the UPDATE is delayed

until no other clients are reading from the table. This affects only

storage engines that use only table-level locking (such as MyISAM,

MEMORY, and MERGE).

o With the IGNORE keyword, the update statement does not abort even if

errors occur during the update. Rows for which duplicate-key

conflicts occur are not updated. Rows for which columns are updated

to values that would cause data conversion errors are updated to the

closest valid values instead.

URL: http://dev.mysql.com/doc/refman/5.5/en/update.html

posted @ 2020-12-31 12:45 AI非AI 阅读(369) 评论(0) 收藏举报

刷新页面返回顶部