一、创建用户
1 mysql> CREATE USER 'sampadm'@'localhost' IDENTIFIED BY 'secret'; 2 Query OK, 0 rows affected (0.02 sec) 3 4 mysql> GRANT ALL ON sampdb.* TO 'sampadm'@'localhost'; 5 Query OK, 0 rows affected (0.00 sec)
GRANT是对USER权限的控制。
二、登录
mysql -u sampadm -p
登录通常模式为：
mysql -h host-name -p -u user-name
如果需要端口号，就加一个-P选项
三、创建数所库
mysql> CREATE DATABASE sampdb; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | sampdb | | test | +--------------------+ 3 rows in set (0.00 sec)
四、创建数表
除了在mysql上用命令直接创建数据表，还可以通过source命令，如下
保存在本地文件create_president.sql
# Create president table for U.S. Historical League DROP TABLE IF EXISTS president; #@ _CREATE_TABLE_ CREATE TABLE president ( last_name VARCHAR(15) NOT NULL, first_name VARCHAR(15) NOT NULL, suffix VARCHAR(5) NULL, city VARCHAR(20) NOT NULL, state VARCHAR(2) NOT NULL, birth DATE NOT NULL, death DATE NULL ); #@ _CREATE_TABLE_
mysql> USE sampdb; Database changed mysql> SOURCE create_president.sql Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.31 sec)
四、查看表结构
mysql> DESC president; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | last_name | varchar(15) | NO | | NULL | | | first_name | varchar(15) | NO | | NULL | | | suffix | varchar(5) | YES | | NULL | | | city | varchar(20) | NO | | NULL | | | state | varchar(2) | NO | | NULL | | | birth | date | NO | | NULL | | | death | date | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> SHOW CREATE TABLE president\G *************************** 1. row *************************** Table: president Create Table: CREATE TABLE `president` ( `last_name` varchar(15) NOT NULL, `first_name` varchar(15) NOT NULL, `suffix` varchar(5) DEFAULT NULL, `city` varchar(20) NOT NULL, `state` varchar(2) NOT NULL, `birth` date NOT NULL, `death` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
五、数据类型
1 、INT表示这个数据列将用来保存整数值（没有小数部分的数字）
2、USIGNED不允许出现负数
3、NOT NULL必须填有数据，不得为空。
4、AUTO_INCREMENT这是MySQL里的一个特殊属性。如果某个数据列拥有这一属性，在插入数据进该表时，没有给出该列的值，MySQL自动生成下一个编号并赋值给这个数据列。可以设定初始值，如下：
CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ... ) ENGINE=InnoDB AUTO_INCREMENT=20130325 DEFAULT CHARSET=utf8
六、SQL语句
１、SQL语句主要分为３个类别
(1)DDL(Data Definition Languages)语句：数据定义语言，这些语言用来定义不同的数据段、数据库、表、列、索引等数据库对象的定义。
常用的关键字是create、drop、alter等。
(2)DML(Data Manipulation Languages)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
常用的语句关键字是insert、delete、update、select等。
(3)DCL(Data Control Languages)语句:数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的关键字有grant 、revoke等。
２、DDL、DML、DCL三种语句
(1)DDL
<1>创建一个数据库CREATE
mysql> create database test1; Query OK, 1 row affected (0.00 sec) mysql> show DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | example | | mysql | | performance_schema | | test | | test1 | +--------------------+ 6 rows in set (0.00 sec)
<2>删除一个数据库DROP
mysql> DROP DATABASE test1; Query OK, 0 rows affected (0.19 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | example | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
<3>创建表CREATE TABLE
格式:
CREATE TABLE tablename ( column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, column_name_3 column_type_3 constraints, ... column_name_n column_type_n constraints );
tablename：是数据表名
column_name：是列名字
column_type：是列数据类型
constraints：这个列的约束条件
mysql> USE example; Database changed mysql> CREATE TABLE emp ( -> ename varchar(10), -> hiredate date, -> sal decimal(10, 2), -> deptno int(2) -> ); Query OK, 0 rows affected (0.63 sec) mysql> SHOW CREATE TABLE emp\G *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `ename` varchar(10) DEFAULT NULL, `hiredate` date DEFAULT NULL, `sal` decimal(10,2) DEFAULT NULL, `deptno` int(2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.06 sec)
<4>删除表
DROP TABLE tablename
<5>修改表
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST|AFTER col_name]
修改emp的ename字段定义，将varchar(10)改为varchar(20)
mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE emp MODIFY COLUMN ename VARCHAR(20); Query OK, 0 rows affected (1.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.08 sec) mysql>
<6>增加字段
ALTER TABLE tablename ADD [COLUMN] column_defination [FIRST|AFTER col_name]
mysql> ALTER TABLE emp ADD COLUMN age INT(3); Query OK, 0 rows affected (0.57 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
<7>删除字段
ALTER TABLE tablename DROP [COLUMN] col_name
mysql> ALTER TABLE emp DROP COLUMN age; Query OK, 0 rows affected (0.63 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
<8>字段改名
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_name_definition [FIRST|AFTER col_name]
mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.05 sec) mysql> ALTER TABLE emp CHANGE sal sale DECIMAL(10, 2); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sale | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
注意：change和modify都可以修改表的定义，不同的是change后面需写两次列名，不方便。但是change的优点是可以修改列的名称，modify则不能。
<9>修改字段排列顺序
mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sale | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> ALTER TABLE emp ADD birth date after ename; Query OK, 0 rows affected (1.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sale | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
修改deptno字段，并将其放在第一。
mysql> ALTER TABLE emp MODIFY deptno INT(3) FIRST; Query OK, 0 rows affected (0.71 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | deptno | int(3) | YES | | NULL | | | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sale | decimal(10,2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
<10>更改表名
ALTER TABLE tablename RENAME [TO] new_tablename;
mysql> ALTER TABLE emp RENAME empl; Query OK, 0 rows affected (0.22 sec) mysql> DESC empl; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | deptno | int(3) | YES | | NULL | | | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sale | decimal(10,2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.02 sec)
(2)DML语句
DML操作是指对数据库中表记录的操作，主要包括表记录的插入(INSERT), 更新(UPDATE),删除(DELETE)和查询（SELECT）,是开发人员日常使用最频繁的操作。
<1>插入记录
INSERT INTO tablename (field1, field2, field3, ...,fieldn) VALUES (value1, value2, value3, ..., valuen);
<2>更新记录
UPDATE tablename SET field1=value1, field2=value2, field3=value3, ..., fieldn=valuen [WHERE CONDITION]
在MySQL可以同时更新多个表中数据，语法如下：
UPDATE t1, t2, t3, ..., tn SET t1.field1=value1, t2.field2=value2, t3.field3=value3, ..., fieldn=exprn [WHERE CONDITION]
<3>删除记录
DELETE FROM tablename [WHERE CONDITION]
<4>查询记录
SELECT * FROM tablename [WHERE CONDITION]
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2[DESC|ASC], ...,fieldn [DESC|ASC]]
SELECT ....[LIMIT offset_start, row_count]
基中offset_start表示记录的起始偏移量，row_count表示显示的行数
SELECT [field1, field2, ..., fieldn] fun_name FROM tablename [WHERE CONDITION] [GROUP BY field1, field2, ..., fieldn [WITH ROLLUP]] [HAVING CONDITION]]
其中，fun_name表示要做的聚合操作，也就是聚合函数，常用的有sum(求和)，count(*)（记录数)，max(最大值)，min（最小值）
GROUP BY 关键字表示要进行分类取合的字段，比如要按照照部门分类统计员工数量，部门就应该写在group by 后面。
WITH ROLLUP是可选语法，表明是否对分类聚合后的结果进行再汇总
HAVING关键字表示对分类后的结果再进行条件的过滤。
表连接，左连接和右连接
SELECT ... FROM ... [LEFT|RIGHT|INNER]JOIN....
子查询
子查询要用到select ，子查询的关键字主要包括in,not in, =, !=, exists,not exists等。
记录联合
SELECT * FROM t1 UNION | UNION ALL SELECT * FROM t2 UNION | UNION ALL .... UNION| UNION ALL SELECT * FROM tn