欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-03-21 15:08:00
...
bitsCN.com


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)

bitsCN.com
相关标签: 数据库 mysql