mysql数据库学习笔记之常用操作命令

1、创建数据库

mysql> create database user;
Query OK, 1 row affected (0.00 sec)

2、使用此数据库

mysql> use user;
Database changed

3、在此数据库上创建表

mysql> create table person(
    -> id int unsigned not null auto_increment primary key,
    -> name varchar(30)
    -> );                 
Query OK, 0 rows affected (0.00 sec)

4、查看此person表的表结构

mysql> desc person;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

5、创建person_bak,并是此表的表结构与person一样，即复制person的表结构

mysql> create table person_bak like person;
Query OK, 0 rows affected (0.01 sec)

6、向person表中插入数据

mysql> insert into person (name) values ("user1");
Query OK, 1 row affected (0.00 sec)

7、将person表中的数据复制到person_bak表中
mysql> insert into person_bak select * from person;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

8、向person表中创建name列的索引
方法一：

mysql> create index in_name on person (name);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

方法二：

mysql> alter table person add index in_name (name);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0
9、查看索引

mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| person |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| person |          1 | in_name  |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

10、在person表中创建唯一索引
mysql> alter table person add unique index un_name (name);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0
11、修改列的属性

mysql> alter table person modify name varchar(20);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0
12、统计表中的数据数据

mysql> select count(*) from person;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)
13、创建一个视图

mysql> create view v_person as select * from person;
Query OK, 0 rows affected (0.01 sec)

14、查看视图(和查看表的命令一样)

当删除表中的某条记录时，相应的此表对应的视图中的满足条件的记录也将会被删除掉

mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| person         |
| person_bak     |
| v_person       |
+----------------+
3 rows in set (0.00 sec)
15、删除视图

mysql> drop view v_person;
Query OK, 0 rows affected (0.00 sec)
16、字符串连接函数---concat("string1","string2") 别名

mysql> select concat("li","haichao") myname;
+-----------+
| myname    |
+-----------+
| lihaichao |
+-----------+
1 row in set (0.00 sec)

17、大写转换成小写的函数---lcase(string1)

mysql> select lcase("LHC");
+--------------+
| lcase("LHC") |
+--------------+
| lhc          |
+--------------+
1 row in set (0.00 sec)

18、将字符串转换成大写的函数----ucase(string1);

mysql> select ucase("lhc");
+--------------+
| ucase("lhc") |
+--------------+
| LHC          |
+--------------+
1 row in set (0.00 sec)

19、判断字符串长度的函数length(string1);

mysql> select length("lhc");
+---------------+
| length("lhc") |
+---------------+
|             3 |
+---------------+
1 row in set (0.02 sec)

20、去除前端和后端的空格函数 ltrim()和rtrim()

21、将指定的字符串重复n次，repeat(string ,count)

mysql> select repeat("linux",3);
+-------------------+
| repeat("linux",3) |
+-------------------+
| linuxlinuxlinux   |
+-------------------+
1 row in set (0.02 sec)
22、字符串替换函数

在"linux is very good"中寻找linux，并将其替换成php

mysql> select replace("linux is very good","linux","php");
+---------------------------------------------+
| replace("linux is very good","linux","php") |
+---------------------------------------------+
| php is very good                            |
+---------------------------------------------+
1 row in set (0.01 sec)

23、字符串截取函数substring("str",int 1,int 2)

在str字符串中从int1开始(从1计)到int2结束(包含),取其字段

mysql> select substring("linux is very good",1,5);
+-------------------------------------+
| substring("linux is very good",1,5) |
+-------------------------------------+
| linux                               |
+-------------------------------------+
1 row in set (0.00 sec)

24、space(）函数：生成空格的函数，通常与concat函数一起使用

mysql> select concat(space(50),"linux");
+---------------------------------------------------------+
| concat(space(50),"linux")                               |
+---------------------------------------------------------+
|                                                   linux |
+---------------------------------------------------------+
1 row in set (0.02 sec)

25、十进制转二进制函数BIN()

mysql> select BIN(255);
+----------+
| BIN(255) |
+----------+
| 11111111 |
+----------+
1 row in set (0.00 sec)

26、向上取整函数CEILING()，比如5.6则为6，向下取整floor(),比如5.6则为5

mysql> select ceiling(5.6);
+--------------+
| ceiling(5.6) |
+--------------+
|            6 |
+--------------+
1 row in set (0.01 sec)

************************************************************************

mysql> select floor(5.6);
+------------+
| floor(5.6) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)
27、取最大值和最小值

select sutdent_name,MIN(test_score),MAX(test_score) from student group by student_name;

28、返回随机数:RAND()

mysql> select ceiling( 10*RAND());
+---------------------+
| ceiling( 10*RAND()) |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.00 sec)