mysql建库的字符集为latin1,客户端的字符集为utf8;

6.1、创建数据库:

1、命令语法:

create database <数据库名称>; #注意数据库名不能以数字开头;

2、命令:

create database lc;

Query OK, 1 row affected (0.11 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| lc |

| mysql |

| performance_schema |

| wordpress |

+--------------------+

5 rows in set (0.19 sec)

6.2、显示数据库:

1、显示所有数据库:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| lc |

| lc1 |

| lc2 |

| mysql |

| performance_schema |

| wordpress |

+--------------------+

7 rows in set (0.00 sec)

2、显示相似数据库:

mysql> show databases like '%lc%' ;

+-----------------+

| Database (%lc%) |

+-----------------+

| lc |

| lc1 |

| lc2 |

+-----------------+

3 rows in set (0.00 sec)

6.3、删除数据库:

mysql> drop database lc2;

Query OK, 0 rows affected (0.00 sec)

mysql> show databases like '%lc2%';

Empty set (0.00 sec)

6.4、连接数据库(相当于linux中cd命令):

mysql> use lc;

Database changed

6.5、显示当前连接的数据库(类似于pwd、whoami命令);

mysql> select database();

+------------+

| database() |

+------------+

| lc |

+------------+

1 row in set (0.00 sec)

6.6、查看当前登录的用户:

mysql> select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

6.7、查看当前数据库的版本:

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.5.62 |

+-----------+

1 row in set (0.00 sec)

6.8、查看当前的时间:

mysql> select now();

+---------------------+

| now() |

+---------------------+

| 2019-02-18 16:05:37 |

+---------------------+

1 row in set (0.00 sec)

6.9、显示连接数据库的表:

mysql> show tables;

Empty set (0.00 sec)

6.10、删除mysql系统多余的账号;

mysql> drop user ''@'localhost'; #主机名是大写的时候会删除不了用户,

#需要是使用下面的删除方法;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='';

Query OK, 1 row affected (0.00 sec)

6.11、建表:

1、语法:

create table <表名> (

<字段名1> <类型1>,

......

<字段名n> <类型n>

);

#建表时使用的数据库指定的编码集;

create table student1 (

id int(16) not null,

name varchar(20) not null,

age int(2) not null,

dept varchar(20) default null

);

mysql> show tables;

+--------------+

| Tables_in_lc |

+--------------+

| student |

+--------------+

1 row in set (0.00 sec)


6、数据库常见管理应用_字段

6.12、mysql表的类型:


6、数据库常见管理应用_字段_02


6、数据库常见管理应用_mysql_03


6、数据库常见管理应用_数据库_04


6、数据库常见管理应用_mysql_05


6、数据库常见管理应用_mysql_06


6、数据库常见管理应用_数据库_07


6、数据库常见管理应用_数据库_08


6、数据库常见管理应用_mysql_09


6、数据库常见管理应用_数据库_10


6、数据库常见管理应用_字段_11

6.13、查看表结构:

1、查看表结构:

mysql> use lc;

Database changed

desc student; show columns from student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(16) | NO | | NULL | |

| name | varchar(20) | NO | | NULL | |

| age | int(2) | NO | | NULL | |

| dept | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.32 sec)

2、查看建表语句:

mysql> show create table student\G; #\G的作用是发送命令给mysql server,以垂直的方式显示结果;

*************************** 1. row ***************************

Table: student

Create Table: CREATE TABLE `student` (

`id` int(16) NOT NULL,

`name` varchar(20) NOT NULL,

`age` int(2) NOT NULL,

`dept` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1 row in set (0.00 sec)


ERROR:

No query specified

3、指定建表的字符集为utf8:

ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='学生表';

6.14、DML语句之insert知识讲解:

create table test (

id int(4) not null auto_increment,

name varchar(20) not null comment '姓名',

age varchar(2) comment '年龄',

primary key (id)

);

1、命令语法:

(1)按表结构的字段插入数据:

insert into <表名> values (值1,值2,值3......),(值1,值2,值3......);

(2)按指定的字段插入数据:

insert into <表名> (字段名1,字段名2,......) values (值1,值2,......),(值1,值2,值3......);

(3)优化:使用批量的插入代替单个的插入数据;

2、应用实例:

mysql> insert into test values (1,'lc','20'),(2,'lc1','21');

mysql> insert into test (name,age) values ('lc3','22'),('lc4','23');

mysql> select * from test;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | lc | 20 |

| 2 | lc1 | 21 |

| 3 | lc3 | 22 |

| 4 | lc4 | 23 |

+----+------+------+

4 rows in set (0.00 sec)

6.15、查看mysql的授权:

mysql> show grants for 'lc'@'localhost';

+------------------------------------------------------------------------+

| Grants for lc@localhost |

+------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lc'@'localhost' IDENTIFIED BY PASSWORD <secret> |

| GRANT ALL PRIVILEGES ON `lc`.* TO 'lc'@'localhost' |

+------------------------------------------------------------------------+

2 rows in set (0.00 sec)

6.16、DML之updata命令:

mysql> select * from test;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | lc | 20 |

| 2 | lc1

| 3 | lc3 | 22 |

| 4 | lc4 | 23 |

| 5 | lc | 22 |

+----+------+------+

5 rows in set (0.00 sec)

mysql> update test set name='lbd' where id=2;

mysql> select * from test;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | lc | 20 |

| 2 | lbd

| 3 | lc3 | 22 |

| 4 | lc4 | 23 |

| 5 | lc | 22 |

+----+------+------+

5 rows in set (0.00 sec)

6.17、DML之删除命令:

1、删除表中指定的内容:

delete from test where id=2 and name='lc';

2、删除表中的内容:

delete from test; #逻辑清除,按行删;

truncate table test; #比delete删的更快,清空物理文件;

6.18、增删改表的字段:

一般用不到,由开发提供该语句;

1、语法:

alter table <表名> add <字段> <类型> <其它>;

2、应用:

参数说明:

after:代表在某字段之后;

first:代表在某字段之前;

(1)删除字段:

drop

(2)增加字段类型:

add

(3)修改字段类型:

modify

(4)修改字段的名称及字段类型:

change

6.19、修改表的操作:

1、修改表的名字:

alter table test1 rename test;

2、删除表:

drop table test;

6.20、防止mysql误操作:

mysql -U:防止误操作,比如使用update命令时需要指定范围where;

[root@db01 tmp]# echo "alias mysql='mysql -U'" >>/etc/profile

[root@db01 tmp]# tail -1 /etc/profile

alias mysql='mysql -U'

[root@db01 tmp]# source /etc/profile