SQL语句分类:
DDL:数据定义语言,定义数据库对象(库,表,列,索引)的操作。
DML:数据操作语言,定义对数据库记录的操作。
DCL:数据控制语言,定义对数据库、表、字段、用户的访问权限和安全级别。(授权grant,收回权限revoke等)。
查询所有的数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gongsi |
| mysql |
| performance_schema |
+--------------------+
这里面中的information_schema主要存储的是系统中的一些数据库的对象信息。(例如:用户表信息,字符集信息。分区信息等)
mysql存储了系统的用户权限信息以及帮助信息。
有时还会自带一个test数据库,测试数据库,任何用户都可以使用。
创建数据库
语法:create database [if not exists] databasename;
如果该数据库已经存在,将会报错。
删除数据库
语法:drop database [if exists ] databasename;
使用该命令需要谨慎。
数据库的选择
语法:use databasename;
数据库的字符集问题
latin1支持西欧字符,希腊字符等。
gbk支持中文简体字符。
big5支持繁体字符。
utf8支持全国所有的语言。
所以一般在建立数据库时需要指明数据库的字符集为utf8.
查看:
mysql> show variables like 'character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
可以看到character_set_database 为latin1 ,所以我需要修改其字符集类型,否则我在建立每一个数据库时都需要在末尾加入 default character-set=‘utf8’;会是及其麻烦的。
修改方法:
在/etc/my.cnf文件的[mysqld]下添加:default-character-set=utf8(当你发现修改完后导致服务启动不了了,有可能是你的mysql版本太高了,5.5之后的需要使用character-set-server=utf8)。
在client下面也添加:default-character-set=utf8
如果使用了rpm格式装的mysql,是不会在/etc下创建my.cnf文件的。那么就需要在/usr/local/mysql下面拷贝一份my.cnf去/etc下即可!。再进行配置。
配置完毕后重启服务!
修改完后的表:
mysql> show variables like 'character%'; +--------------------------+---------------------------------------------------------------+ | 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 | /usr/local/mysql-5.6.32-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------+ 8 rows in set (0.00 sec)
查看当前在哪个数据库
mysql> select database();
+------------+
| database() |
+------------+
| kaoshi |
+------------+
1 row in set (0.00 sec)
查看指定数据库的信息
mysql> show create database kaoshi;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| kaoshi | CREATE DATABASE `kaoshi` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
查看数据库的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.32 |
+-----------+
1 row in set (0.00 sec)
与Linux交互不用登出系统执行命令:
mysql> system cat /etc/passwd;
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
........
查看该数据库有多少表
mysql> show tables;
+------------------+
| Tables_in_kaoshi |
+------------------+
| kaoshi |
| test1 |
| test2 |
| test3 |
| workInfo |
+------------------+
5 rows in set (0.00 sec)
查看数据表中各列的信息
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| pass | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
或者使用:
mysql> show create table test1\G;
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`pass` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
删除表
语法:drop table [if exists ] tablename;
修改表的类型
修改列的类型:alter table tablename modify 列名 列类型;
增加列:alter table tablename add 列名 列类型;
删除列:alter table tablename drop 列名 ;
列改名:alter table 表名 change 旧列名 新列名 列类型;
更改表名:alter table tablename rename newtablename;或者使用:rename table 表名 to newtablename;
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| pass | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table test1 modify id int(2);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 add name varchar(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 change id ID int(11);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 rename text;
Query OK, 0 rows affected (0.00 sec)
mysql> desc text;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| pass | varchar(50) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> rename table text to test1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| pass | varchar(50) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
复制一个表
语法一:create table newtablename like oldtablename;
语法二:create table newtablename select * from oldtablename;
mysql> create table test3 like test2;
ERROR 1050 (42S01): Table 'test3' already exists
mysql> create table test4 like test2;
Query OK, 0 rows affected (0.04 sec)
mysql> desc test4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| num | int(11) | YES | | NULL | |
| pass | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from kaoshi;
+------+--------+
| id | name |
+------+--------+
| 1 | gaofan |
| 1 | gaofan |
| 2 | tom |
+------+--------+
3 rows in set (0.06 sec)
mysql> create table test5 select * from kaoshi limit 2;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test5;
+------+--------+
| id | name |
+------+--------+
| 1 | gaofan |
| 1 | gaofan |
+------+--------+
2 rows in set (0.01 sec)