1、mysql使用
1)客户端命令:命令在客户端执行,不需要命令终止符,通常默认为分号“;”
\q|quit|exit
\?|?|h|help
\g:把命令发送给服务器端执行,相当于“;”
\G:把行分段显示
\c:取消命令执行mysql> SELECT user,host,password from mysql.user\g
+------+------------+-------------------------------------------+
| user | host | password |
+------+------------+-------------------------------------------+
| root | localhost | *CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D |
| root | BAIYU\_179 | |
| root | 127.0.0.1 | *CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D |
| | BAIYU\_179 | |
+------+------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT user,host,password from mysql.user\G
*************************** 1. row ***************************
user: root
host: localhost
password: *CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D
*************************** 2. row ***************************
user: root
host: BAIYU\_179
password:
*************************** 3. row ***************************
user: root
host: 127.0.0.1
password: *CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D
*************************** 4. row ***************************
user:
host: BAIYU\_179
password:
4 rows in set (0.00 sec)
2)服务器端命令:命令发送到服务器端执行
BIF:内建函数
SELECT,
选取选部分行)和投影(选部分列)
select col1,col2,...from tb1,tb2,...where clause;
where col1 > 30;
SHOW DATABASES; 显示有权限查看的所有库
USE database_name:使用某库
SHOW TABLES; 显示其库所有表
CREATE DATABASE database_name;创建库
DROP DATABASE database_name;删除某库mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 00:10:18 |
+----------------+
1 row in set (0.00 sec)
命令帮助的获取:mysql> help KEYWORD
命令不区分字符大小写(建议大写),但与文件系统相关的部分是否区分大小写根据OS
3)mysql使用:
交互模式:
非交互|批处理模式:[[email protected]_179 ~]# mysql -uroot -p123456789 -e ‘SELECT USER();‘
+----------------+
| USER() |
+----------------+
| [email protected] |
+----------------+
[[email protected]_179 ~]# echo -e "SHOW DATABASES;\nCREATE DATABASE testdb;\nSHOW DAT
ABASES;" > a.sql
[[email protected]_179 ~]# mysql -uroot -p123456789
Database
information_schema
mysql
performance_schema
test
Database
information_schema
mysql
performance_schema
test
testdb
4)键:key
约束:constraint
主键约束:对一张表来讲,主键只能有一个,主键数据不能相同,主键不能为空(NULL)
唯一键约束:可以为NULL,可以有多个
外键约束:
检查式约束:用户自定义有效取值范围
键:就是选取出来的字段
主键:能唯一标识表中每一个记录的字段或字段的组合
候选键:能作为主键的字段,
5)表:table
由行和列组成的而为关系
字段:字段名,约束,字段类型
创建表:
CREATE TABLE table_name(col1_name col1_type 修饰符,col2 col2_type 修饰符...);
删除表:
DROP TABLE table_name;
字段或字段类型还可以有修饰符:
NOT NULL:不能为空
NULL:可为空
UNSIGNED:无符号,无负值
DEFAULT:
AUTO_INCREMENT:自动增长类型的字段必须为主键或唯一键
PRIMARY KEY:主键,如何把多个字段定义为主键则用括号把多个字段括起来在后面加PRIMARY KEY
字符要加上引号,数值不能加引号mysql> CREATE TABLE students(name varchar(30),age tinyint,gender ENUM(‘f‘,‘m‘))
;
Query OK, 0 rows affected (0.02 sec)
mysql> DESCRIBE students;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum(‘f‘,‘m‘) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> CREATE TABLE students(name varchar(30) NOT NULL,age tinyint UNSIGNED NOT
NULL,gender ENUM(‘F‘,‘M‘) DEFAULT ‘M‘);
Query OK, 0 rows affected (0.00 sec)
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name | varchar(30) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum(‘F‘,‘M‘) | YES | | M | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE students(stuID int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY
KEY, name varchar(30) NOT NULL,age tinyint UNSIGNED NOT NULL,gender ENUM(‘F‘,‘
M‘) NOT NULL DEFAULT ‘M‘);
Query OK, 0 rows affected (0.01 sec)
mysql> DESC students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum(‘F‘,‘M‘) | NO | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
6)数据
插入:
INSERT INTO table_name(col1,col2,...) value|values(val1,val2,...)
INSERT INTO table_name VALUES(val1,val2,...)mysql> INSERT INTO students values(1,‘xj‘,25,‘M‘);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM students;
+-------+------+-----+--------+
| stuID | name | age | gender |
+-------+------+-----+--------+
| 1 | xj | 25 | M |
+-------+------+-----+--------+
1 row in set (0.00 sec)
mysql> INSERT INTO students values(2,‘xxj‘,23,‘M‘),(3,‘xiexiaojun‘,24,‘M‘);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM students;
+-------+------------+-----+--------+
| stuID | name | age | gender |
+-------+------------+-----+--------+
| 1 | xj | 25 | M |
| 2 | xxj | 23 | M |
| 3 | xiexiaojun | 24 | M |
+-------+------------+-----+--------+
3 rows in set (0.00 sec)
mysql> INSERT INTO students(name,gender) value(‘xx‘,‘M‘),(‘xiexie‘,‘F‘);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> SELECT * FROM students;
+-------+------------+-----+--------+
| stuID | name | age | gender |
+-------+------------+-----+--------+
| 1 | xj | 25 | M |
| 2 | xxj | 23 | M |
| 3 | xiexiaojun | 24 | M |
| 4 | xx | 0 | M |
| 5 | xiexie | 0 | F |
+-------+------------+-----+--------+
5 rows in set (0.00 sec)
查询:
SELECT col1,col2,... FROM table_name WHERE; 条件表达式;
SELECT * FROM table_name;mysql> SELECT name,age,gender FROM students WHERE age>20;
+------------+-----+--------+
| name | age | gender |
+------------+-----+--------+
| xj | 25 | M |
| xxj | 23 | M |
| xiexiaojun | 24 | M |
+------------+-----+--------+
3 rows in set (0.00 sec)
mysql> SELECT name,age,gender FROM students WHERE gender=F;
ERROR 1054 (42S22): Unknown column ‘F‘ in ‘where clause‘
mysql> SELECT name,age,gender FROM students WHERE gender=‘F‘;
+--------+-----+--------+
| name | age | gender |
+--------+-----+--------+
| xiexie | 0 | F |
+--------+-----+--------+
1 row in set (0.00 sec)
mysql> SELECT name,age,gender FROM students WHERE age<25 AND gender=‘F‘;
+--------+-----+--------+
| name | age | gender |
+--------+-----+--------+
| xiexie | 0 | F |
+--------+-----+--------+
1 row in set (0.00 sec)
删除:
DELETE FROM table_name;
DELETE FORM table_name WHERE 条件表达式;
LIKE:匹配通配符
%:任意长度的任意字符
_:任意单个字符
RLIKE:正则表达式mysql> DELETE FROM students WHERE age<20;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM students;
+-------+------------+-----+--------+
| stuID | name | age | gender |
+-------+------------+-----+--------+
| 1 | xj | 25 | M |
| 2 | xxj | 23 | M |
| 3 | xiexiaojun | 24 | M |
+-------+------------+-----+--------+
3 rows in set (0.00 sec)
mysql> DELETE FROM students WHERE NAME LIKE ‘%o%‘;
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM students;
+-------+------+-----+--------+
| stuID | name | age | gender |
+-------+------+-----+--------+
| 1 | xj | 25 | M |
| 2 | xxj | 23 | M |
+-------+------+-----+--------+
2 rows in set (0.00 sec)
更新:
UPDATE table_name SET col1=values,col2=values,...;
UPDATE table_name SET col1=values,... WHERE 条件表达式;mysql> UPDATE students SET age=30;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM students;
+-------+------+-----+--------+
| stuID | name | age | gender |
+-------+------+-----+--------+
| 1 | xj | 30 | M |
| 2 | xxj | 30 | M |
+-------+------+-----+--------+
2 rows in set (0.00 sec)
mysql> UPDATE students SET age=33 WHERE STUID=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM students;
+-------+------+-----+--------+
| stuID | name | age | gender |
+-------+------+-----+--------+
| 1 | xj | 33 | M |
| 2 | xxj | 30 | M |
+-------+------+-----+--------+
2 rows in set (0.00 sec)
2、字段|数据类型
字符型:
char(N):固定长度,N指字符数,不区分大小写
varchar(N):可变长度,N指最大字符数,不区分大小写
binary(N):区分大小写
varbinary(N):区分大小写
text(N):大段文本,不区分大小写
blob(N):大段文本,区分大小写
数值型:
精确数值型:
int(整形):tinyint(微,1B),smallint(小,2B),mediumint(中,3B),int(4B),bigint(大,4B)
decimal(十进制)
近似数值型:
float:单精度
double:双精度
日期时间型:
date,3B
time,3B
datetime,8B
timestamp:时间戳
布尔型:
NULL:
内置类型:
ENUM:枚举型(一定范围内跳一个,字符型)
SET:集合型(集合内字符任意组合,不能使用集合外的字符)
数据类型的作用:
比较方式:排序方式
存储空间:取值范围
参与的运算:
原文:http://xiexiaojun.blog.51cto.com/2305291/1710127