mysql數據庫學習筆記之常用操作命令
1、創建數據庫 www.2cto.com
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表的表結構 www.2cto.com
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、查看索引 www.2cto.com
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)
