首页 > 代码库 > MySQL基本语句的使用
MySQL基本语句的使用
一、几个简单命令介绍
1.show [global | session] variables，默认为session
作用：显示服务器参数变量，有些变量值可以修改，能改变mysql的工作特性；有些可以动态调整，即刻生效；另外一些只能修改配置文件后，重启生效。
2.show [global | session] status
作用：显示服务器状态变量，记录了当前包括过去的时间内mysql的运行统计数据
二、添加MAN手册
[root@client ~]# vim /etc/man.config MANPATH /usr/local/mysql/man
连接头文件
[root@client ~]# ln –sv /usr/local/mysql/include /usr/include/mysql [root@client ~]# vim/etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib [root@client ~]# ldconfig –v | grep mysql
三、客户端连接MySQL
1.安装完成后，有个root用户，默认密码为空
[root@client ~]# mysql -uroot -p Enter password:
2.查看有哪些数据库
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.03 sec)
注意：MySQL客户端执行命令时候，无需语句结束符；服务器端命令（特指SQL语句），必须有语句结束符，默认为分号。
3.选择要使用的数据库
mysql> use mysql Database changed
这里选择使用安装后即存在的名为mysql数据库，我们选择其中的user表来查询相关信息
mysql> SELECT user,host,password FROMuser; +------+------------+----------+ | user | host | password | +------+------------+----------+ | root | localhost | | | root | client.com | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | client.com | | +------+------------+----------+ 6 rows in set (0.00 sec)
4.使用帮助命令
mysql> help For information about MySQL products andservices, visit: http://www.mysql.com/ For developer information, including theMySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training,or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be firston line and end with ‘;‘ ? (\?) Synonym for `help‘. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don‘t write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlogwith multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don‘t show warnings afterevery statement. For server side help, type ‘help contents‘
四、用户管理
1.创建用户
格式：CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
mysql> CREATE USER ‘pyz‘@‘192.168.5.%‘IDENTIFIED BY ‘mypass‘; Query OK, 0 rows affected (0.00 sec)
注意：这里主机IP地址中的“%”表示匹配任意长度的任意字符
修改数据后刷新
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
再次查看user表中的字段，发现新增的用户“pyz”被赋予了密码
mysql> SELECT user,host,password FROMuser; +------+----------------+-------------------------------------------+ | user | host | password | +------+----------------+-------------------------------------------+ | root | localhost | | | root | client.com | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | client.com | | | pyz | 192.168.5.0/24 | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +------+----------------+-------------------------------------------+ 7 rows in set (0.00 sec)
2.用户授权
格式：GRANT ALL ON dbname.tablename TO ‘username‘@‘host‘;
例子：给来自192.168.5.0/24网段的pyz用户授予test数据库中所有表的所有权限
mysql> GRANT ALL ON test.* TO‘pyz‘@‘192.168.5.%‘; Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
再次查看所以数据库，多了名为test的数据库
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
切换到test数据库
mysql> USE test; Database changed mysql> SELECT * FROM test; ERROR 1146 (42S02): Table ‘test.test‘doesn‘t exist mysql> \q //退出数据库 Bye
提示test数据库下的test表不存在
3.修改用户密码
方法一：SQL语句修改SET PASSWORD FOR username@host=PASSWORD（’password’）;
方法二：SHELL下修改mysqladmin -uusername -p password ‘password‘
例子：修改root用户的密码为mypasswd
[root@client ~]# mysqladmin -uroot -p password‘mypasswd‘ Enter password: //输入修改前的密码
再次连接mysql，提示需要输入密码
[root@client ~]# mysql ERROR 1045 (28000): Access denied for user‘root‘@‘localhost‘ (using password: NO)
以输入用户名和密码的方式连接MySQL
[root@client~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.33-log MySQL CommunityServer (GPL) Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or its affiliates. Other names may be trademarksof their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ toclear the current input statement.
切换到mysql数据库
mysql> use mysql Database changed mysql> SELECT user,host,password FROM user; +------+----------------+-------------------------------------------+ | user | host | password | +------+----------------+-------------------------------------------+ | root | localhost | *85E26B8AB29FEE8453201A3511DAE24A24059109| | root | client.com | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | client.com | | | pyz | 192.168.5.0/24 | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.%.% |*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | +------+----------------+-------------------------------------------+ 9 rows in set (0.01 sec)
4.删除用户
为了安全起见，这里我们删除了密码为空的用户
格式：DROP USER ‘uname‘@‘host‘;
mysql> DROP USER ‘pyz‘@‘192.168.5.0/24‘; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host,password FROMuser; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | client.com | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | client.com | | | pyz | 192.168.5.%.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | +------+---------------+-------------------------------------------+ 8 rows in set (0.00 sec)
mysql> DROP USER ‘root‘@‘::1‘; Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host,password FROMuser; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | client.com | | | root | 127.0.0.1 | | | | localhost | | | | client.com | | | pyz | 192.168.5.%.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | +------+---------------+-------------------------------------------+ 7 rows in set (0.00 sec)
删除匿名用户
mysql> DROP USER ‘‘@‘localhost‘; Query OK, 0 rows affected (0.01 sec) mysql> DROP USER ‘‘@‘client.com‘; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host,password FROMuser; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | client.com | | | root | 127.0.0.1 | | | pyz | 192.168.5.%.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | +------+---------------+-------------------------------------------+ 5 rows in set (0.00 sec)
另一种为用户添加密码验证的示例
mysql> SET PASSWORD FOR‘root‘@‘client.com‘=PASSWORD(‘mypasswd‘); Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR‘root‘@‘127.0.0.1‘=PASSWORD(‘mypasswd‘); Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host,password FROM user; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | client.com | *85E26B8AB29FEE8453201A3511DAE24A24059109| | root | 127.0.0.1 |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | pyz | 192.168.5.%.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | +------+---------------+-------------------------------------------+ 5 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT DATABASE() -> \g +------------+ | DATABASE() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
五、相关命令讲解
quit，\q：退出
go，\g：将命令发送到server
delimiter，\d：指定终止符，例如：mysql> \d //将之前的终止符“；”换成“//”
use，\u：设置默认数据库
ego，\G：将每一行的数据纵向显示
system，\!：不退出sql查看linux系统文件，例如：>\! ls /root
status，\s：显示当前工作状态
MySQL两种执行方式
交互方式：-h，--host=
-u，--user=
-p，--password=
-D db_name，--database= //将数据库设为默认数据库
-e，’SQL_statement’ //不登陆sql，只运行命令，取回结果
例：
[root@client~]# mysql --user=root --host=localhost --password=mypasswd --database=mysql -e‘SELECT user,host,password FROM user;‘ +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | client.com | *85E26B8AB29FEE8453201A3511DAE24A24059109| | root | 127.0.0.1 | *85E26B8AB29FEE8453201A3511DAE24A24059109| | pyz | 192.168.5.%.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | +------+---------------+-------------------------------------------+
批处理方式：（使用SQL脚本）
mysqloption < scripts.sql （输入重定向）
mysql> source /path/to/scripts.sql （要有权限读取）
例：
[root@client ~]# vim test.sql CREATE DATABASE wpdb; CREATE USER ‘wpuser’@’192.168.5.%’IDENTIFIED BY ‘wppass’ CREATE USER ‘wpuser’@’localhost’ IDENTIFIEDBY ‘wppass’ CREATE USER ‘wpuser’@’127.0.0.1’ IDENTIFIEDBY ‘wppass’ GRANT ALL ON wpdb.* TO‘wpuser‘@‘192.168.5.%‘; GRANT ALL ON wpdb.* TO ‘wpuser‘@‘ localhost‘; GRANT ALL ON wpdb.* TO ‘wpuser‘@‘127.0.0.1‘; "test.sql" [New] 7L, 332Cwritten
读入脚本
[root@client ~]# mysql -uroot -pmypasswd< test.sql [root@client ~]# mysql -uroot -pmypasswd Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.5.33-log MySQL CommunityServer (GPL) Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or its affiliates. Other names may be trademarksof their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ toclear the current input statement.
查看数据库
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | wpdb | +--------------------+ 5 rows in set (0.00 sec)
再次查看，发现新增了用户
mysql> use mysql Database changed mysql> SELECT user,host,password FROMuser; +--------+---------------+-------------------------------------------+ | user | host | password | +--------+---------------+-------------------------------------------+ | root | localhost |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | client.com |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | root | 127.0.0.1 |*85E26B8AB29FEE8453201A3511DAE24A24059109 | | wpuser | 192.168.5.% | *C9B2DB1CA193280B971CA3602D5174A5D637D2BF| | pyz | 192.168.5.%.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | pyz | 192.168.5.% | | | wpuser | localhost |*C9B2DB1CA193280B971CA3602D5174A5D637D2BF | | wpuser | 127.0.0.1 |*C9B2DB1CA193280B971CA3602D5174A5D637D2BF | +--------+---------------+-------------------------------------------+ 8 rows in set (0.00 sec)
六、MySQL配置文件存放路径
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_BASE/my.cnf
~/.my.cnf
客户端每次登陆MySQL时都需要输入用户名和密码，为了简化这个过程，我们可在用户家目录下新建一个.my.cnf文件，将用户名和密码写入其中
例：
[root@client ~]# cd [root@client ~]# vim .my.cnf [client] user=root host=localhost password=mypass
修改权限
[root@client ~]# chmod 600 .my.cnf [root@client ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.5.33-log MySQL CommunityServer (GPL) Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or its affiliates. Other names may be trademarksof their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ toclear the current input statement.
七、字符集
1.显示所支持的所有字符集
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 |
| 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 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.03 sec)
2.显示所有字符集的排序规则
mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id |Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4| Yes | Yes | 1 |
| cp850_bin | cp850 | 80| | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7| Yes | Yes | 1 |
| koi8r_bin | koi8r | 74| | Yes | 1 |
| latin1_german1_ci | latin1 | 5| | Yes | 1 |
| latin1_swedish_ci | latin1 | 8| Yes | Yes | 1 |
| latin1_danish_ci | latin1 | 15 | | Yes | 1 |
| latin1_german2_ci | latin1 | 31| | Yes | 2 |
| latin1_bin | latin1 | 47| | Yes | 1 |
| latin1_general_ci | latin1 | 48| | Yes | 1 |
| latin1_general_cs | latin1 | 49| | Yes | 1 |
| latin1_spanish_ci | latin1 | 94| | Yes | 1 |
| latin2_czech_cs | latin2 | 2| | Yes | 4 |
| latin2_general_ci | latin2 | 9| Yes | Yes | 1 |
| latin2_hungarian_ci | latin2 | 21 | | Yes | 1 |
| latin2_croatian_ci | latin2 | 27| | Yes | 1 |
| latin2_bin | latin2 | 77 | | Yes | 1 |
| swe7_swedish_ci | swe7 | 10 | Yes | Yes | 1 |
| swe7_bin | swe7 | 82 | | Yes | 1 |
| ascii_general_ci | ascii | 11| Yes | Yes | 1 |
| ascii_bin | ascii | 65| | Yes | 1 |
| ujis_japanese_ci | ujis | 12 | Yes | Yes | 1 |
| ujis_bin | ujis | 91 | | Yes | 1 |
| sjis_japanese_ci | sjis | 13 | Yes | Yes | 1 |
| sjis_bin | sjis | 88 | | Yes | 1 |
| hebrew_general_ci |hebrew | 16 | Yes | Yes | 1 |
| hebrew_bin | hebrew | 71| | Yes | 1 |
| tis620_thai_ci | tis620 | 18| Yes | Yes | 4 |
| tis620_bin | tis620 | 89| | Yes | 1 |
| euckr_korean_ci | euckr | 19| Yes | Yes | 1 |
| euckr_bin | euckr | 85| | Yes | 1 |
| koi8u_general_ci | koi8u | 22| Yes | Yes | 1 |
| koi8u_bin | koi8u | 75| | Yes | 1 |
| gb2312_chinese_ci | gb2312 | 24| Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86| | Yes | 1 |
| greek_general_ci | greek | 25 | Yes | Yes | 1 |
| greek_bin | greek | 70| | Yes | 1 |
| cp1250_general_ci | cp1250 | 26| Yes | Yes | 1 |
| cp1250_czech_cs | cp1250 | 34| | Yes | 2 |
| cp1250_croatian_ci | cp1250 | 44| | Yes | 1 |
| cp1250_bin | cp1250 | 66| | Yes | 1 |
| cp1250_polish_ci | cp1250 | 99| | Yes | 1 |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
| latin5_turkish_ci | latin5 | 30| Yes | Yes | 1 |
| latin5_bin | latin5 | 78 | | Yes | 1 |
| armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 |
| armscii8_bin | armscii8 | 64 | | Yes | 1 |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci |utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 |
| ucs2_bin | ucs2 | 90 | | Yes | 1 |
| ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 |
| ucs2_icelandic_ci |ucs2 | 129 | | Yes | 8 |
| ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 |
| ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 |
| ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 |
| ucs2_polish_ci | ucs2 | 133 | | Yes | 8 |
| ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 |
| ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 |
| ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 |
| ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 |
| ucs2_czech_ci | ucs2 | 138 | | Yes | 8 |
| ucs2_danish_ci | ucs2 | 139 | | Yes | 8 |
| ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 |
| ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 |
| ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 |
| ucs2_roman_ci | ucs2 | 143 | | Yes | 8 |
| ucs2_persian_ci | ucs2 | 144 | | Yes | 8 |
| ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 |
| ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 |
| ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 |
| ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 |
| cp866_general_ci | cp866 | 36| Yes | Yes | 1 |
| cp866_bin | cp866 | 68| | Yes | 1 |
| keybcs2_general_ci | keybcs2 | 37 |Yes | Yes | 1 |
| keybcs2_bin | keybcs2 | 73| | Yes | 1 |
| macce_general_ci | macce | 38| Yes | Yes | 1 |
| macce_bin | macce | 43| | Yes | 1 |
| macroman_general_ci | macroman | 39 | Yes | Yes | 1 |
| macroman_bin | macroman | 53 | | Yes | 1 |
| cp852_general_ci | cp852 | 40| Yes | Yes | 1 |
| cp852_bin | cp852 | 81 | | Yes | 1 |
| latin7_estonian_cs | latin7 | 20| | Yes | 1 |
| latin7_general_ci | latin7 | 41| Yes | Yes | 1 |
| latin7_general_cs | latin7 | 42| | Yes | 1 |
| latin7_bin | latin7 | 79| | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 |Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46| | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci |utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 |
| cp1251_ukrainian_ci | cp1251 | 23 | | Yes | 1 |
| cp1251_bin | cp1251 | 50 | | Yes | 1 |
| cp1251_general_ci | cp1251 | 51| Yes | Yes | 1 |
| cp1251_general_cs | cp1251 | 52| | Yes | 1 |
| utf16_general_ci | utf16 | 54| Yes | Yes | 1 |
| utf16_bin | utf16 | 55| | Yes | 1 |
| utf16_unicode_ci | utf16 | 101 | | Yes | 8 |
| utf16_icelandic_ci | utf16 | 102 | | Yes | 8 |
| utf16_latvian_ci | utf16 | 103 | | Yes | 8 |
| utf16_romanian_ci | utf16 | 104 | | Yes | 8 |
| utf16_slovenian_ci |utf16 | 105 | | Yes | 8 |
| utf16_polish_ci | utf16 | 106 | | Yes | 8 |
| utf16_estonian_ci | utf16 | 107 | | Yes | 8 |
| utf16_spanish_ci | utf16 | 108 | | Yes | 8 |
| utf16_swedish_ci | utf16 | 109 | | Yes | 8 |
| utf16_turkish_ci | utf16 | 110 | | Yes | 8 |
| utf16_czech_ci | utf16 | 111 | | Yes | 8 |
| utf16_danish_ci | utf16 | 112 | | Yes | 8 |
| utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 |
| utf16_slovak_ci | utf16 | 114 | | Yes | 8 |
| utf16_spanish2_ci |utf16 | 115 | | Yes | 8 |
| utf16_roman_ci | utf16 | 116 | | Yes | 8 |
| utf16_persian_ci | utf16 | 117 | | Yes | 8 |
| utf16_esperanto_ci | utf16 | 118 | | Yes | 8 |
| utf16_hungarian_ci | utf16 | 119 | | Yes | 8 |
| utf16_sinhala_ci | utf16 | 120 | | Yes | 8 |
| cp1256_general_ci | cp1256 | 57| Yes | Yes | 1 |
| cp1256_bin | cp1256 | 67| | Yes | 1 |
| cp1257_lithuanian_ci | cp1257 | 29 | | Yes | 1 |
| cp1257_bin | cp1257 | 58| | Yes | 1 |
| cp1257_general_ci |cp1257 | 59 | Yes | Yes | 1 |
| utf32_general_ci | utf32 | 60| Yes | Yes | 1 |
| utf32_bin | utf32 | 61| | Yes | 1 |
| utf32_unicode_ci | utf32 | 160 | | Yes | 8 |
| utf32_icelandic_ci | utf32 | 161 | | Yes | 8 |
| utf32_latvian_ci | utf32 | 162 | | Yes | 8 |
| utf32_romanian_ci | utf32 | 163 | | Yes | 8 |
| utf32_slovenian_ci | utf32 | 164 | | Yes | 8 |
| utf32_polish_ci | utf32 | 165 | | Yes | 8 |
| utf32_estonian_ci |utf32 | 166 | | Yes | 8 |
| utf32_spanish_ci | utf32 | 167 | | Yes | 8 |
| utf32_swedish_ci | utf32 | 168 | | Yes | 8 |
| utf32_turkish_ci | utf32 | 169 | | Yes | 8 |
| utf32_czech_ci | utf32 | 170 | | Yes | 8 |
| utf32_danish_ci | utf32 | 171 | | Yes | 8 |
| utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 |
| utf32_slovak_ci | utf32 | 173 | | Yes | 8 |
| utf32_spanish2_ci | utf32 | 174 | | Yes | 8 |
| utf32_roman_ci | utf32 | 175 | | Yes | 8 |
| utf32_persian_ci | utf32 | 176 | | Yes | 8 |
| utf32_esperanto_ci | utf32 | 177 | | Yes | 8 |
| utf32_hungarian_ci | utf32 | 178 | | Yes | 8 |
| utf32_sinhala_ci | utf32 | 179 | | Yes | 8 |
| binary | binary | 63| Yes | Yes | 1 |
| geostd8_general_ci | geostd8 | 92 |Yes | Yes | 1 |
| geostd8_bin | geostd8 | 93| | Yes | 1 |
| cp932_japanese_ci | cp932 | 95| Yes | Yes | 1 |
| cp932_bin | cp932 | 96| | Yes | 1 |
| eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 |
+--------------------------+----------+-----+---------+----------+---------+
197 rows in set (0.00 sec)
八、表的操作
1.创建表
格式：CREATE TABLE dbname.tablename(col1 defination, col2 defination, col3 defination,…, key defination);
mysql> CREATE TABLE test.students (NameCHAR(30) NOT NULL,id TINYINT UNSIGNED,Age TINYINT UNSIGNED,Class VARCHAR(20)NOT NULL,PRIMARY KEY (Name)); Query OK, 0 rows affected (0.08 sec)
查看刚才创建的表
mysql>USE test Database changed mysql>DESC students;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra|
+-------+---------------------+------+-----+---------+-------+
| Name | char(30) | NO | PRI | NULL | |
| id | tinyint(3) unsigned | YES | | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| Class | varchar(20) | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
查看索引
mysql>SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | Name | A | 0 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
以纵向的方式查看数据
mysql>SHOW INDEX FROM students\G
*************************** 1. row***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
mysql> SELECT Name,id,Age,Class FROM students; Empty set (0.00 sec)
mysql> INSERT INTO students(name,id,class) values (‘Jerry‘,1,‘2 class‘); Query OK, 1 row affected (0.02 sec)
mysql> SELECT Name,id,class FROMstudents;
+-------+------+---------+
| Name | id | class |
+-------+------+---------+
| Jerry | 1 | 2 class |
+-------+------+---------+
1 row in set (0.00 sec)
mysql> INSERT INTO students values(‘Tom‘,2,30,‘1 class‘); Query OK, 1 row affected (0.01 sec)
mysql> SELECT Name,id,class FROMstudents;
+-------+------+---------+
| Name | id | class |
+-------+------+---------+
| Jerry | 1 | 2 class |
| Tom | 2 | 1 class |
+-------+------+---------+
2 rows in set (0.00 sec)
mysql> SELECT Name,id,Age,class FROMstudents;
+-------+------+------+---------+
| Name | id | Age | class |
+-------+------+------+---------+
| Jerry | 1 | NULL | 2 class |
| Tom | 2 | 30 | 1 class |
+-------+------+------+---------+
2 rows in set (0.00 sec)
mysql> INSERT INTO students(name,id,class) values (‘Blair‘,3,‘2 class‘); Query OK, 1 row affected (0.01 sec)
mysql> SELECT Name,id,Age,class FROMstudents;
+-------+------+------+---------+
| Name | id | Age | class |
+-------+------+------+---------+
| Blair | 3 | NULL | 2 class |
| Jerry | 1 | NULL | 2 class |
| Tom | 2 | 30 | 1 class |
+-------+------+------+---------+
3 rows in set (0.00 sec)
九、实例演示
1.创建一个名为testdb的数据库
mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.00 sec)
mysql> USE testdb; Database changed
2.创建一个名为ta1的表，并设计表的结构
mysql> CREATE TABLE testdb.tb1 (IDTINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(30) NOT NULL UNIQUEKEY,Age TINYINT UNSIGN)mysql> CREATE TABLE testdb.tb1 (ID TINYINT NOT NULLPRIMARY KEY AUTO_INCREMENT,Name CHAR(30) NOT NULL UNIQUE KEY,Age TINYINTUNSIGNED,Gender CHAR(1) DEFAULT ‘M‘,Course VARCHAR(50) NOT NULL); Query OK, 0 rows affected (0.01 sec)
查看刚才创建的表
mysql> DESC tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key| Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| ID | tinyint(4) | NO | PRI | NULL | auto_increment |
| Name | char(30) | NO | UNI | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| Gender | char(1) | YES | |M | |
| Course | varchar(50) | NO | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
3.插入数据
mysql> INSERT INTO tb1 values(1,‘LingHuchong‘,24,‘Male‘,‘Hamogong‘); Query OK, 1 row affected, 1 warning (0.01sec) mysql> INSERT INTO tb1 values(2,‘HuangRong‘,19,‘Female‘,‘ChilianShenzhang‘); Query OK, 1 row affected, 1 warning (0.01sec) mysql> INSERT INTO tb1 values(3,‘LuWushuang‘,18,‘Female‘,‘JiuyangShengong‘); Query OK, 1 row affected, 1 warning (0.03sec) mysql> INSERT INTO tb1 values(4,‘ZhuZiliu‘,52,‘Male‘,‘PiXieJianfa‘); Query OK, 1 row affected, 1 warning (0.00sec) mysql> INSERT INTO tb1 values(5,‘ChenJialuo‘,22,‘Female‘,‘XianglongShibazhang‘); Query OK, 1 row affected, 1 warning (0.01sec)
4.查看刚才插入的数据
mysql> SELECT ID,Name,Age,Gender,CourseFROM tb1;
+----+-------------+------+--------+---------------------+
| ID | Name | Age | Gender | Course |
+----+-------------+------+--------+---------------------+
| 1| LingHuchong | 24 | M | Hamogong |
| 2| HuangRong | 19 | F | ChilianShenzhang |
| 3| LuWushuang | 18 | F | JiuyangShengong |
| 4| ZhuZiliu | 52 | M | PiXieJianfa |
| 5| ChenJialuo | 22 | F | XianglongShibazhang |
+----+-------------+------+--------+---------------------+
5 rows in set (0.00 sec)
5.完成以下操作：
（1）找出性别为女性的所有人
mysql> SELECT Name FROM tb1 WHEREGender=‘F‘;
+------------+
| Name |
+------------+
| HuangRong |
| LuWushuang |
| ChenJialuo |
+------------+
3 rows in set (0.00 sec)
（2）找出年龄大于20的所有人
mysql> SELECT Name FROM tb1 WHEREAge>20;
+-------------+
| Name |
+-------------+
| LingHuchong |
| ZhuZiliu |
| ChenJialuo |
+-------------+
3 rows in set (0.00 sec)
（3）修改ZhuZiliu的Course为KuihuaBaodian
mysql> UPDATE tb1 SETCourse=‘KuihuaBaodian‘ WHERE Name=‘ZhuZiliu‘;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
（4）删除年龄小于等于19的所有人
mysql> DELETE FROM tb1 WHERE Age<=19; Query OK, 2 rows affected (0.01 sec)
mysql> SELECT ID,Name,Age,Gender,CourseFROM tb1;
+----+-------------+------+--------+---------------------+
| ID | Name | Age | Gender | Course |
+----+-------------+------+--------+---------------------+
| 1| LingHuchong | 24 | M | Hamogong |
| 4| ZhuZiliu | 52 | M | KuihuaBaodian |
| 5| ChenJialuo | 22 | F | XianglongShibazhang |
+----+-------------+------+--------+---------------------+
3 rows in set (0.00 sec)
（5）授权给testuser对testdb库所有表的所以权限
mysql> GRANT ALL ON testdb.* TO‘testuser‘@‘%‘; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye