MySql基本使用

原创已于 2024-12-16 15:14:52 修改 · 643 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2016-07-13 21:03:55 首次发布

MySQL 专栏收录该内容

5 篇文章

订阅专栏

文章目录

1 列类型
2 增删改查sql语句
- 2.1 插入数据
3 函数
- 3.1 字符串
- - 3.1.1 字符串函数CONCAT_WS与CONCAT()用法
4 信息查看
- 4.1 查看当前自己信息
- 4.2 查看Client的字符集

1 列类型

1.1 varchar

MySQL的varchar定义长度到底是字节还是字符？？

varchar存储规则：
4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）
5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节
Mysql4中最大也不过是20个字节,但是Mysql5根据编码不同,存储大小也不同。

1.2 将字段设置为unique

 select * from qq_admin;
+----------+------------+----------------------------------+
| admin_id | admin_name | admin_passwd                     |
+----------+------------+----------------------------------+
|        1 | sk         | e10adc3949ba59abbe56e057f20f883e |
|        2 | sk         | e10adc3949ba59abbe56e057f20f883e |
+----------+------------+----------------------------------+

 alter table qq_admin add unique key(admin_name);
ERROR 1062 (23000): Duplicate entry 'sk' for key 'admin_name'

 select * from qq_admin;
+----------+------------+----------------------------------+
| admin_id | admin_name | admin_passwd                     |
+----------+------------+----------------------------------+
|        1 | sk         | e10adc3949ba59abbe56e057f20f883e |
|        2 | sk1        | e10adc3949ba59abbe56e057f20f883e |
+----------+------------+----------------------------------+

 alter table qq_admin add unique key(admin_name);
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into qq_admin(admin_name,admin_passwd) values('sk',md5('123456'));
ERROR 1062 (23000): Duplicate entry 'sk' for key 'admin_name'

1.3 字段设置缺省值

建表时：create table tablename (columnname datatype default defaultvalue);
已建表修改：alter table tablename alter column columnname set default defaultvalue;

演示：
这里写图片描述

2 增删改查sql语句

2.1 插入数据

创建表

 create table sanguo(
    -> id int,
    -> sname varchar(20));
Query OK, 0 rows affected (0.29 sec)

插入数据

mysql> insert into sanguo values
    -> (1,'liubei'),
    -> (2,'zhugeliang'),
    -> (3,'guanyu');
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

显示结果

mysql> select * from sanguo;
+------+------------+
| id   | sname      |
+------+------------+
|    1 | liubei     |
|    2 | zhugeliang |
|    3 | guanyu     |
+------+------------+
3 rows in set (0.00 sec)

3 函数

3.1 字符串

3.1.1 字符串函数CONCAT_WS与CONCAT()用法

CONCAT(str1,str2,…)
方法
CONCAT(str1,str2,…)
说明
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)。
代码如下:

mysql> select concat('exam','ple')
    -> ;
+----------------------+
| concat('exam','ple') |
+----------------------+
| example              |
+----------------------+
1 row in set (0.00 sec)

mysql> select concat(' ','Catch','a','failing','start')；
    -> ；
    -> ^C
mysql> select concat(' ','Catch','a','failing','start');
+-------------------------------------------+
| concat(' ','Catch','a','failing','start') |
+-------------------------------------------+
|  Catchafailingstart                       |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(' ','Catch','a','failing','start');
+----------------------------------------------+
| concat_ws(' ','Catch','a','failing','start') |
+----------------------------------------------+
| Catch a failing start                        |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat('My','NULL','QL');
+--------------------------+
| concat('My','NULL','QL') |
+--------------------------+
| MyNULLQL                 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select concat('My',NULL,'QL');
+------------------------+
| concat('My',NULL,'QL') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

mysql> select concat(143.3)
    -> ;
+---------------+
| concat(143.3) |
+---------------+
| 143.3         |
+---------------+
1 row in set (0.00 sec)

CONCAT_WS(separator,str1,str2,…)
方法
CONCAT_WS(separator,str1,str2,…)
说明
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

代码如下:

mysql> select concat_ws(',','First name','Second name','Last name');
+-------------------------------------------------------+
| concat_ws(',','First name','Second name','Last name') |
+-------------------------------------------------------+
| First name,Second name,Last name                      |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(',','First name',NULL,'Last name');
+----------------------------------------------+
| concat_ws(',','First name',NULL,'Last name') |
+----------------------------------------------+
| First name,Last name                         |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(',','First name',NULL,'       ','Last name');
+--------------------------------------------------------+
| concat_ws(',','First name',NULL,'       ','Last name') |
+--------------------------------------------------------+
| First name,       ,Last name                           |
+--------------------------------------------------------+
1 row in set (0.00 sec)

4 信息查看

4.1 查看当前自己信息

mysql> \s
--------------
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe  Ver 14.14 Distrib 5.7.13, for Win64 (x86_64)

Connection id:          2
Current database:       practice
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.13-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 49 min 18 sec

Threads: 1  Questions: 36  Slow queries: 0  Opens: 114  Flush tables: 1  Open tables: 103  Queries per second avg: 0.012
--------------

4.2 查看Client的字符集

mysql> show variables like '%char%'
    -> ;
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)