MySQL|DDL

启动mysql服务
#] mysqld_safe --defaults-file=/opt/soft/mysql8/my.cnf &

连接本地数据库
#] mysql -uroot -p
password:

连接远程数据库
#] mysql -uroot -h192.168.78.141 -p
password: 
 
SQL语句分类
DDL: CREATE DROP ALTER
DML: INSERT UPDATE DELETE
DQL: SELECT
DCL: GRANT COMMIT ROLLBACK

创建数据库
mysql> CREATE DATABASE KB21;
mysql> CREATE DATABASE IF NOT EXISTS KB21;
删除数据库
mysql> DROP DATABASE KB21;
mysql> DROP DATABASE IF EXISTS KB21;
查看所有数据库名
mysql> show databases;
选中指定数据库，使用KB21数据库
mysql> use kb21;
查看当前使用的数据库
mysql> select database();

创建表语句
mysql> CREATE TABLE `STU`(`ID` INT(10), `NAME` VARCHAR(32));
mysql> CREATE TABLE IF NOT EXISTS `teacher`(`id` int(10),`name` varchar(32));

查看表的结构
mysql> DESC STU;
mysql> desc teacher;
查看建表语句
mysql> SHOW CREATE TABLE STU;
mysql> show create table teacher;
查看当前库中所有的表
mysql> SHOW TABLES;
mysql> show tables;

插入数据
mysql> insert into stu(id,name) values(1,'zs');
mysql> insert into stu(id,name) values(2,"ls"),(3,"ww"),(4,"zl");

查询数据
mysql> select * from stu;

删除表
mysql> drop table if exists stu;
mysql> drop table if exists teacher;

创建表
mysql> create table if not exists student(`id` int(10) primary key auto_increment, `name` varchar(32) not null comment "学生姓名", `age` int(10) not null comment "学生年龄", `email` varchar(255) comment "学生邮箱");

mysql> create table if not exists teacher(`id` int(10) primary key auto_increment,`name` varchar(32) not null comment "老师姓名", `age` int not null comment "年龄", tel varchar(32) not null default '021-43579802' comment "联系电话", `email` varchar(255), school varchar(100) default "njdx");

mysql> insert into teacher(`name`,`age`) value('zls',29);
mysql> insert into teacher(`name`,`age`,`tel`,email,school) value('zls',29,'15850500365','lj@qq.com','bdqn');

插入数据，字段设置为not null(不允许为空)时,必须设置值
mysql> insert into student(`name`,`age`) value('zs',18);
mysql> insert into student(`name`,`age`,`email`) values('ls',19,'ls@qq.com');

数据库中表列值类型
   数值类型
   字符串类型
   日期和时间型数值类型
   NULL值

mysql> select * from teacher where email is not null;
+----+------+-----+-------------+-----------+--------+
| id | name | age | tel         | email     | school |
+----+------+-----+-------------+-----------+--------+
|  2 | zls  |  29 | 15850500365 | lj@qq.com | bdqn   |
+----+------+-----+-------------+-----------+--------+
1 row in set (0.00 sec)

mysql> select * from teacher where email is null;
+----+------+-----+--------------+-------+--------+
| id | name | age | tel          | email | school |
+----+------+-----+--------------+-------+--------+
|  1 | zls  |  29 | 021-43579802 | NULL  | njdx   |
+----+------+-----+--------------+-------+--------+
1 row in set (0.00 sec)

create table if not exists student(
`StudentNO` INT(4) NOT NULL COMMENT "学号",
`LoginPwd` VARCHAR(20) DEFAULT NULL,
# SADFASDFASDASDFsdafasd
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT "学生姓名",
`Sex` TINYINT(1) DEFAULT NULL COMMENT "性别，取值0或1",
`GradeId` INT(11) DEFAULT NULL COMMENT "年级编号",
`Phone` VARCHAR(50) NOT NULL COMMENT "联系电话",
`Address` VARCHAR(255) NOT NULL COMMENT "地址",
`BornDate` DATETIME DEFAULT NULL COMMENT "出生时间",
`Email` VARCHAR(50) NOT NULL COMMENT "邮箱账号",
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT "身份证号"
) COMMENT "学生信息表";

设置引擎类型  MyISAM InnoDB
mysql> create table if not exists demo01(id int(10), name varchar(32)) engine=MyISAM;

修改表名
mysql> ALTER TABLE demo01 RENAME AS demo1;
修改字段
mysql> ALTER TABLE demo1 CHANGE name uname varchar(50) not null default '未知';
在表中新增列
mysql> ALTER TABLE demo1 ADD upwd varchar(50) not null default '123456';
删除表中某一列
mysql> ALTER TABLE demo1 DROP upwd;