mysql基础学习

标红色为固定写法，熟记即可

登录mysql

mysql -uMysql服务器ip地址 -u用户名 -p密码

root@localhost ~ # mysql -uroot -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12464
Server version: 5.1.60-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

显示所有数据库

创建数据库

mysql> create database xuexi;
Query OK, 1 row affected (0.06 sec)
mysql>

使用数据库

mysql> use xuexi;
Database changed
mysql>

显示所有表

mysql> show tables;
Empty set (0.00 sec)
mysql>

创建表study共4列

mysql> create table study(
-> id int(11) auto_increment primary key,
-> username varchar(50),
-> sex enum('M','F'),
-> addtime int(11)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>

显示表

mysql> show tables;
+-----------------+
| Tables_in_xuexi |
+-----------------+
| study |
+-----------------+
1 row in set (0.00 sec)
查询表的结构

mysql> desc study;
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | YES | | NULL | |
| sex | enum('M','F') | YES | | NULL | |
| addtime | int(11) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)
mysql>

添加数据每个逗号隔开的为一列或一个值，要列与值的数量匹配

mysql> insert into study values('','username1','M',unix_timestamp());
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> insert into study values('','username2','M',unix_timestamp());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into study(username,sex,addtime) values('username3','M',unix_timestamp());
Query OK, 1 row affected (0.00 sec)

如果不小心输入了单引号或者双引号，那么再次输入一个单引号或者双引号退出错误

mysql> select *from study1'
'> where
'> id
'> =1
'>
'>
'>
'> ';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''
where
id
=1
'' at line 1
mysql>

查询表的所有数据

mysql> select *from study;
+----+-----------+------+------------+
| id | username | sex | addtime |
+----+-----------+------+------------+
| 1 | username1 | M | 1479697164 |
| 2 | username2 | M | 1479697168 |
| 3 | username3 | M | 1479697195 |
+----+-----------+------+------------+
3 rows in set (0.00 sec)

查询指定列的所有数据

mysql> select username,sex from study;
+-----------+------+
| username | sex |
+-----------+------+
| username1 | M |
| username2 | M |
| username3 | M |
+-----------+------+
3 rows in set (0.00 sec)

查询username的值为username1的数据

mysql> select *from study where username='username1';
+----+-----------+------+------------+
| id | username | sex | addtime |
+----+-----------+------+------------+
| 1 | username1 | M | 1479697164 |
+----+-----------+------+------------+
1 row in set (0.00 sec)

使用时间函数

mysql> select *,from_unixtime(addtime) from study where username='username1';
+----+-----------+------+------------+------------------------+
| id | username | sex | addtime | from_unixtime(addtime) |
+----+-----------+------+------------+------------------------+
| 1 | username1 | M | 1479697164 | 2016-11-21 10:59:24 |
+----+-----------+------+------------+------------------------+
1 row in set (0.00 sec)
mysql>

查询username的值为username11的记录

mysql> select *from study where username='username11';
Empty set (0.00 sec)

更新

mysql> update study set username='username11' where username='username1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

查询

mysql> select *from study where username='username11';
+----+------------+------+------------+
| id | username | sex | addtime |
+----+------------+------+------------+
| 1 | username11 | M | 1479697164 |
+----+------------+------+------------+
1 row in set (0.00 sec)

删除

mysql> delete from study where username='username11';

Query OK, 1 row affected (0.00 sec)

显示

mysql> select *from study where username='username11';
Empty set (0.00 sec)
mysql>

结构修改

mysql> alter table study drop test;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc study;
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | YES | | NULL | |
| sex | enum('M','F') | YES | | NULL | |
| addtime | int(11) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table study add test varchar(50) after sex;

mysql> alter table study change test test1 int(11);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc study;
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | YES | | NULL | |
| sex | enum('M','F') | YES | | NULL | |
| test1 | int(11) | YES | | NULL | |
| addtime | int(11) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql>

修改列的类型
mysql> alter table study modify test1 varchar(50);

修改表名

mysql> alter table study rename studynew;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_xuexi |
+-----------------+
| studynew |
+-----------------+
1 row in set (0.00 sec)
mysql>

mysql日期操作

日期型例如:2016-11-22 17:13:20

时间戳例如:1479806000

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-11-22 16:44:28 |
+---------------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1479804287 |
+------------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2016-11-22 |
+-------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1479804353 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2016-11-22 16:46:27 |
+---------------------------------+
1 row in set (0.01 sec)

mysql> select from_unixtime(unix_timestamp(now()));

+--------------------------------------+
| from_unixtime(unix_timestamp(now())) |
+--------------------------------------+
| 2016-11-22 16:46:48 |
+--------------------------------------+

1 row in set (0.00 sec)

mysql> select year(date(now()));
+-------------------+
| year(date(now())) |
+-------------------+
| 2016 |
+-------------------+
1 row in set (0.00 sec)

mysql> select month(date(now()));
+--------------------+
| month(date(now())) |
+--------------------+
| 11 |
+--------------------+
1 row in set (0.00 sec)

mysql> select day(date(now()));
+------------------+
| day(date(now())) |
+------------------+
| 22 |
+------------------+
1 row in set (0.00 sec)

mysql> select replace(link,substr(link,instr(link,"?"),100),'') from dh_minigw where type_id =1;

发表评论取消回复

微信