首页 > 代码库 > mysql基础语句整理
mysql基础语句整理
显示所有数据库。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| zabbix |
+--------------------+
4 rows in set (0.00 sec)
新建一个数据库。
mysql> create database dragon;
Query OK, 1 row affected (0.00 sec)
mysql> drop database dragon;
Query OK, 0 rows affected (0.04 sec)
使用切换需要操作的数据库。
mysql> use test;
Database changed
新建表。
mysql> create table test(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> sex BOOLEAN
-> );
Query OK, 0 rows affected (0.03 sec)
显示表的架构。
mysql> DESC test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
显示当前数据库的表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
插入数据。
mysql> INSERT iNTO test(name,sex) VALUES (‘dragon‘,‘1‘);
Query OK, 1 row affected (0.00 sec)
显示 test表的所有字段。
mysql> SELECT * FROM test ;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | dragon | 1 |
+----+--------+------+
1 row in set (0.00 sec)
显示年龄在20到30之间的studentname。
mysql> select studentname ,age BETWEEN 20 AND 30 from user;
+-------------+-------------------------+
| studentname | age BETWEEN 20 AND 30 |
+-------------+-------------------------+
| xueqi | 0 |
| abc | 1 |
| cba | 1 |
+-------------+-------------------------+
3 rows in set (0.00 sec)
更新name是dragon的为xueqi。
mysql> UPDATE test SET name=‘xueqi‘ where name="dragon";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除address为haizhu的的条目。
mysql> delete from user where address=‘haizhu‘;
Query OK, 1 row affected (0.00 sec)
清空表，user表依然存在。
mysql> delete * from user ;
Query OK, 1 row affected (0.00 sec)
为字段改名。
mysql> ALTER TABLE user CHANGE name studentname VARCHAR(20);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
改变字段的数据类型
mysql> ALTER TABLE user MODIFY name VARCHAR(30);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
增加字段，使用(FIRST,AFTER)可以指定排序。
mysql> ALTER TABLE user ADD address VARCHAR(20);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| studentname | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| age | int(10) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec
删除字段。
mysql> ALTER TABLE user DROP address;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
修改表的存储引擎
mysql> ALTER TABLE user ENGINE=MyiSAM;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
新建mysql用户
mysql> CREATE USER ‘test‘@‘locahost‘ IDENTIFIED BY ‘123‘;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO ‘test1‘@‘localhost‘ IDENTIFIED BY ‘123‘;
Query OK, 0 rows affected (0.00 sec)
ALL代表赋予全部权限，可以使用(insert,update,select,delect.etc)细化管理用户的权限，第一个"*"代表所有的数据库，可以在这里为用户指定单独的数据库。第二个"*"代表"*."里所有的表收用户的权限作用，‘test1‘@‘localhost‘代表"test1"用户只能在本地登陆，IDENTIFIED BY ‘123‘设置登陆密码为“123”。
删除用户。
mysql> DROP USER ‘test1‘@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)
刷新数据库。
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
本文出自 “龙爱雪琪” 博客，请务必保留此出处http://dragon123.blog.51cto.com/9152073/1563942
mysql基础语句整理