连接mysql

使用命令行窗口连接mysql数据库

语法：mysql –h主机名 –u用户名 –p密码

说明：-h参数指定数据库ip，本地服务器可以用localhost，-u参数指定用户名，-p参数指定用户密码。

注意：-p和密码值之间有空格的话会让你再一次输入密码。-h参数和-u参数和参数值之间可以有空格也可以没有空格。

mysql -h localhost -uroot -p123456，密码正确的情况下，可以一次登录成功
mysql -hlocalhost -uroot -p123456，密码正确的情况下，可以一次登录成功
mysql -h localhost -uroot -p 123456，密码正确的情况下，需要再次输入密码

sql语句分类

在学习下面的知识之前，先来了解sql的语句分类，mysql的sql语句主要分成四大类。

ddl ： 数据定义语句 (create alter drop)

dml ： 数据操作语句 (insert update delete)

dql ： 数据查询语句(select)

dcl ： 数据控制语句(grant revoke set commit rollback)

用户与授权

用户维护

创建用户

语法：create user ‘username’@‘host’ identified by ‘password’;

说明：username 填入创建的用户名，host填入该用户可以从哪个主机登陆，如果是本地用户可用localhost，如果允许用户可以从任意远程主机登陆，可以使用通配符%。password填入用户密码。

mysql> create user 'dev01'@'%' identified by '123456';query ok, 0 rows affected (0.35 sec)

查看系统用户

语法：select host,user from databasename.tablename;

说明：mysql将系统用户存储在数据库名为mysql，表名为user的表中。

mysql> select host,user from mysql.user;+-------------------+-------+| host        | user  |+-------------------+-------+| %         | dev01 || %         | root  || 127.0.0.1     | root  || ::1        | root  || localhost     |    || localhost     | root  |+-------------------+-------+6 rows in set (0.00 sec)

删除用户

语法：drop user ‘username’@‘host’;

说明：username和host参数说明同上。

mysql> drop user 'dev01'@'%';query ok, 0 rows affected (0.03 sec)

修改用户密码

语法：set password for ‘username’@‘host’ = password(‘newpassword’);

如果是当前用户登录用set password = password(‘newpassword’);

说明：给用户重置密码用for，newpassword填入新密码，其他参数同上。

mysql> set password for 'dev01'@'%' =password('654321');query ok, 0 rows affected (0.06 sec)

用户授权

用户授权

语法：grant privileges on databasename.tablename to ‘username’@‘host’ with grant option;

说明： privileges 填入用户的操作权限，如select ， insert ， update 等，如果要授予所的权限则使用all privileges；databasename 填入数据库名，tablename填入表名，如果要授予该用户对所有数据库和表的相应操作权限则可填入*.*；username 填入创建的用户名;host填入该用户可以从哪个主机登陆。

mysql> grant all privileges on test.* to 'dev01'@'%' with grant option;query ok, 0 rows affected (0.06 sec)

用户权限更改后用flush privileges命令刷新权限。

mysql> flush privileges;query ok, 0 rows affected (0.18 sec)

删除授权

语法：revoke privilege on databasename.tablename from ‘username’@‘host’;

说明： 授权给某个用户用to，从某个用户上取消授权用from，其他参数说明同上述用户授权。

mysql> revoke all privileges on test.* from 'dev01'@'%';query ok, 0 rows affected (0.00 sec)

用户权限更改后用flush privileges命令刷新权限。

数据库维护

创建数据库

语法：create database db_name character set ‘character_name’ collate ‘collate_name’;

说明：db_name填入数据库的名称；character_name填入字符集，一般情况下要选择一个支持中文的字符集，如utf8；collate_name填入排序规则，通常每种字符集都有对应的排序规则。

mysql> create database mysql_test character set 'utf8mb4' collate 'utf8mb4_general_ci';query ok, 1 row affected (0.04 sec)

上述例子创建了一个名称为mysql_test，字符集为utf8mb4 ，排序规则为utf8mb4_general_ci的数据库。开始mysql的utf8编码只支持最多3字节的数据，在5.5.3版本之后增加了utf8mb4编码，mb4就是most bytes 4的意思，专门来存储4个字节的数据。

查看删除数据库

查看系统有哪些数据库，用 show databases命令。

mysql> show databases;+--------------------+| database      |+--------------------+| information_schema || mysql        || mysql_test     || performance_schema |+--------------------+4 rows in set (0.00 sec)

删除数据库用drop databases ‘db_name’;

mysql> drop database mysql_test;query ok, 0 rows affected (0.13 sec)

数据表维护

创建数据表

语法：

create table table_name(field_name1  datatype,field_name2 datatype,field_name3 datatype,primary key ( field_name1 ))character set character_name collate collate_name engine engine_name;

说明： table_name定义表的名称，field_name定义字段的名称，datatype定义字段的类型，primary key ()定义表的主键，character_name指定字符集，collate_name指定排序规则，engine_name指定存储引擎。

也可以什么都不指定，我们在上面创建数据库的时候已经指定了数据库的字符集和排序规则，建表的时候也可以不指定字符集和排序规则，则表的字符集和排序规则默认是数据库的字符集和排序规则。

mysql> create table stu_info ( id int, name varchar(10), age tinyint );query ok, 0 rows affected (0.07 sec)mysql> show tables;+----------------------+| tables_in_mysql_test |+----------------------+| stu_info       |+----------------------+1 row in set (0.00 sec)

一般创建表的时候需要指定字段的类型、字段的长度、是否是主键、是否允许为空、默认值、字符集、排序规则、字段注释。下面是一个比较完整的创建表的语句。

mysql> create table stu_info5-> (-> id int not null default 0 comment '主键',-> name varchar(10) not null default '' comment '姓名',-> age tinyint not null default 0 comment '年龄',-> primary key(id)-> )character set 'utf8mb4' collate 'utf8mb4_general_ci' engine innodb;
query ok, 0 rows affected (0.08 sec)

插入数据到表

语法：

insert into table_name
(field_name1,field_name2,field_name3) 
values
(field_name1_value,field_name2_value,field_name3_value);

说明：table_name指定要插入数据的表，field_name指定要插入数据的列名称，field_name_value指定要插入的列的值。

mysql> insert into stu_info(id,name,age) values(1,'zhangsan',20);
query ok, 1 row affected (0.02 sec)
mysql> insert into stu_info(id,name,age) values(2,'lisi',21);
query ok, 1 row affected (0.00 sec)

查询数据表

查询表使用select语句，查询表的全部字段

mysql> select * from stu_info;+------+----------+------+| id  | name   | age  |+------+----------+------+|   1 | zhangsan |  20 ||   2 | lisi   |  21 |+------+----------+------+2 rows in set (0.00 sec)

查询表指定的列

mysql> select id,name from stu_info;+------+----------+| id  | name   |+------+----------+|   1 | zhangsan ||   2 | lisi   |+------+----------+2 rows in set (0.00 sec)

修改数据表

给表新增字段语法如下

alter table 表名称 add column 字段名称 字段类型 是否为空 default 默认值 comment 字段注释;

例如：

mysql> alter table stu_info5 add column address varchar(32) not null default '' comment '家庭地址';
query ok, 0 rows affected (0.17 sec)

修改表的某个字段语法类似新增字段，要把add column 改为 modify column。例如

mysql> alter table stu_info5 modify column address varchar(64) not null default '' comment '家庭详细地址';
query ok, 0 rows affected (0.14 sec)

删除数据表

删除表使用drop table 语句

mysql> drop table stu_info5;
query ok, 0 rows affected (0.01 sec)

字符集和排序规则

在前文的学习中，我们已经知道创建数据库和表的时候通常需要指定字符集和排序规则。字符集用来定义数据以什么样编码存储，常见的字符集有utf8、utf8mb4、utf16、utf32、gbk、gb2312等。

而排序规则指定了字符之间采用什么样的规则比较、用什么样的规则排序。常见的排序规则有utf8_unicode_ci和utf8_general_ci，ci即是case insensitive，不区分大小写的意思。字符集和排序规则会影响到索引的使用，后面我们会讲到。

查看字符集

使用 show variables like ‘character%’ 语句可以查看系统的字符集。

mysql> show variables like 'character%';+--------------------------+----------------------------+| 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/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

其中几个说明：

character_set_client，即客户端所使用的字符集，mysql client发送给mysql的语句使用的字符集。

character_set_connection，是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。

character_set_database，mysql服务器中某个数据库的字符集，可以给不同的数据库设置不同的字符集。如果建库时没有指明，将使用服务器安装时指定的字符集设置。

character_set_results，mysql在返回查询结果时使用的字符集。如果没有指明，使用服务器默认的字符集。

character_set_server，数据库服务器的字符集，是服务器安装时指定的默认字符集设定。

character_set_system，是数据库系统使用的字符集设定。

查看mysql_test数据库的字符集

mysql> use mysql_test;database changedmysql> show variables like 'character_set_database';+------------------------+---------+| variable_name      | value  |+------------------------+---------+| character_set_database | utf8mb4 |+------------------------+---------+1 row in set (0.00 sec)

或者通过查看mysql_test数据库的创建语句来查看数据库的字符集

mysql> show create database mysql_test;+------------+------------------------------------------------------------------------+| database  | create database                             |+------------+------------------------------------------------------------------------+| mysql_test | create database `mysql_test` /*!40100 default character set utf8mb4 */ |+------------+------------------------------------------------------------------------+1 row in set (0.00 sec)

查看某个表的字符集

mysql> show create table stu_info;+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table   | create table                                                                            |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| stu_info | create table `stu_info` (`id` int(11) default null,`name` varchar(10) default null,`age` tinyint(4) default null) engine=innodb default charset=utf8mb4 |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

修改字符集

修改mysql_test数据库的字符集

mysql> alter database mysql_test character set utf8;query ok, 1 row affected (0.01 sec)mysql> show create database mysql_test;+------------+---------------------------------------------------------------------+| database  | create database                           |+------------+---------------------------------------------------------------------+| mysql_test | create database `mysql_test` /*!40100 default character set utf8 */ |+------------+---------------------------------------------------------------------+1 row in set (0.00 sec)

修改stu_info表的字符集

mysql> alter table stu_info convert to character set utf8;query ok, 2 rows affected (0.09 sec)records: 2  duplicates: 0  warnings: 0mysql> show create table stu_info;+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+| table   | create table                                                                          |+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+| stu_info | create table `stu_info` (`id` int(11) default null,`name` varchar(10) default null,`age` tinyint(4) default null) engine=innodb default charset=utf8 |+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

查看排序规则

使用show variables like ‘collation_%’ 命令可以查看系统的排序规则。

mysql> show variables like 'collation_%';+----------------------+-------------------+| variable_name     | value       |+----------------------+-------------------+| collation_connection | utf8_general_ci  || collation_database  | latin1_swedish_ci || collation_server   | latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.00 sec)

查看mysql_test数据库的排序规则

mysql> use mysql_test;database changedmysql> show variables like 'collation_%';+----------------------+-------------------+| variable_name     | value       |+----------------------+-------------------+| collation_connection | utf8_general_ci  || collation_database  | utf8_general_ci  || collation_server   | latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.00 sec)

查看stu_info表的排序规则，使命show full columns from stu_info 语句。

修改排序规则

修改mysql_test数据库的排序规则

mysql> alter database mysql_test collate utf8_unicode_ci;query ok, 1 row affected (0.08 sec)mysql> show create database mysql_test;+------------+---------------------------------------------------------------------------------------------+| database  | create database                                       |+------------+---------------------------------------------------------------------------------------------+| mysql_test | create database `mysql_test` /*!40100 default character set utf8 collate utf8_unicode_ci */ |+------------+---------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

修改stu_info表的排序规则

alter table stu_info convert to character set utf8 collate utf8_unicode_ci;

再查看下表的排序规则

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

| database | create database |

±-----------±--------------------------------------------------------------------------------------------+

| mysql_test | create database mysql_test /*!40100 default character set utf8 collate utf8_unicode_ci */ |

±-----------±--------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

修改stu_info表的排序规则```java
alter table stu_info convert to character set utf8 collate utf8_unicode_ci;

再查看下表的排序规则